Cannot Insert Record with ID=0

Databases
1

(Sorry for all of the database questions lately. Just doing some “Spring Cleaning” before I go live!!) :cool:

So one of the things I have been doing is checking to be sure I have Indexes and Foreign Keys on the right fields. And in a few cases, I forgot to set a FK for Lookup Tables.

One of these instances is on my “visitor_log” table, which includes…


- id
- member_viewed_id (U1)(FK)
- visitor_id (U2)(FK)
- ip
- hostname
- created_on (U3)
- updated_on

For this table, each FK maps back to my “member” table.

Now, I had a FK for “member_viewed_id” and all was well. But I forgot to set up a FK for “visitor_id”.

The problem is that if a NON-MEMBER visits someone’s profile, then I do an INSERT but just stick a “0” in for the “visitor_id”.

Having numerous records with ID=0 means that creating a FK won’t work, since there is no corresponding record in the “member” table.

I thought this would be easy enough, by creating a “dummy member” with ID=0, but when I do an INSERT - using phpMyAdmin - it is getting numbered as the greatest ID.

Here are the field specs…


Field	Type		Attributes	Null	Default
id	mediumint(8)	UNSIGNED	No	auto_increment

Any suggestions what to do here?

1.) Can I have a Member ID = 0 ??

2.) Is there a way to have a Foreign Key that doesn’t blow up when my “visitor_log” table has an ID=0 but there is no member with an ID=0??

It would be an enormous task if I have to change all of my code to insert some non-zero number for non-members, so I am hoping some of the things I mentioned above are reasonable workarounds… :-/

Sincerely,

Debbie

2

Seems like you should just insert NULL instead of 0 for non-members.

3

So that would mean that in my “visitor_log” table, I would have to change the “visitor_id” field “Not Null” to “Null”.

Sure that is okay?

BTW, here is a screenshot of this table in phpMyAdmin as it may be helpful to you…

Sincerely,

Debbie

4

Yes, “visitor_id” would have to be nullable, which you can do simply by omitting “NOT NULL”.

Whether it’s OK for a value to be nullable depends entirely on your application’s logic. In this case, your application records anonymous visits, where the visitor won’t always have an ID. So yes, it’s OK – even correct – to allow visitor_id to be null.

5

Okay, and to be sure that I understand this…

So I change the field “visitor_id” to allow NULL values.

Then I set up my FK between visitor_log.visitor_id (FK) and member.id (PK).

So, when a non-member looks at my member profile, then my PHP code runs an INSERT which adds a record with a “visitor_id” = NULL

Now, is it correct that my FK relationship won’t complain that there is a NULL on the “visitor_log.visitor_id” side and therefore no record to be found on the “member.id” side?

Are there any additional settings or preferences that I’d have to add to the FK Constraint/index?

(In phpMyAdmin, I just have the ON_UPDATE set to CASCADE…)

Sincerely,

Debbie

6

Correct.

Now, is it correct that my FK relationship won’t complain that there is a NULL on the “visitor_log.visitor_id” side and therefore no record to be found on the “member.id” side?

Also correct.

Are there any additional settings or preferences that I’d have to add to the FK Constraint/index?

Nope.

7

Thanks for the help, Jeff! :tup:

Debbie

8

depends…

what do you want should happen if you delete a row in the member table?

any FK values in related tables will no longer be valid

you need to choose an action here (or go with the default)

9

I suppose if a Member (parent) record is deleted, then the corresponding Visitor_Log (child) record should be deleted.

That leads to a few questions…

1.) If when a non-member visits a Member’s profile, I insert a record in the “visitor_log” with a “visitor_id” of NULL, then there is no corresponding parent record in the “member” table.

So, in that case, there could never be a case of an “orphan” because by design, all entries in the “visitor_log” for non-members are orphan records, right?

So no need for cascading or whatever there, right?

Off Topic:

I am using phpMyAdmin - I know you are a hater of that… :wink:

2.) For my “visitor_log” table, both Foreign Keys (i.e. "member_viewed_id ", “visitor_id”) set to ON UPDATE = CASCADE

Presumably that is what I’d want, right?

3.) In the past in phpMyAdmin, I have tried setting Foreign Keys also to ON DELETE = CASCADE, but when I save things and come back into the Foreign Key view, phpMyAdmin always sets things back to ON DELETE = –

Why does that happen?

Presumably, you would typically want to delete a Child record when you delete a Parent record, although I can see cases where that would not be true.

I can’t see myself ever deleting a “member” record, but if I did, I suppose I would want to also delete the corresponding “visitor_log” child record.

So how do I do that in phpMyAdmin??

Sincerely,

Debbie