SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Cannot Insert Record with ID=0

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

    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...
    Code:
    - 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...
    Code:
    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. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,278
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Seems like you should just insert NULL instead of 0 for non-members.
    "First make it work. Then make it better."

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    Seems like you should just insert NULL instead of 0 for non-members.
    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...

    Attachment 61943


    Sincerely,


    Debbie

  4. #4
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,278
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So that would mean that in my "visitor_log" table, I would have to change the "visitor_id" field "Not Null" to "Null".
    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.
    "First make it work. Then make it better."

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.
    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. #6
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,278
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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
    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.
    "First make it work. Then make it better."

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help, Jeff!


    Debbie

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Are there any additional settings or preferences that I'd have to add to the FK Constraint/index?
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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)

    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...



    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •