SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    primary key being null

    could a null value be a primary key?
    Free Science Homework Help
    http://www.physicsforums.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not? say you have a primary key column, start the first with a null value and then auto increment so...

    null
    1
    2
    3
    ...
    Free Science Homework Help
    http://www.physicsforums.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    why not? because a primary key may not be null, that's why not

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    whats the problem with my example? I don't see why a primary key can't be null
    Free Science Homework Help
    http://www.physicsforums.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i invite you to test it

    in fact, here, borrow my test code --
    Code:
    create table nullprimarykey
    ( id integer primary key auto_increment
    , foo varchar(9)
    );
    insert into nullprimarykey (foo) 
    values ('curly'),('larry'),('moe')
    ;
    insert into nullprimarykey (id,foo) 
    values (null,'shemp');
    this may come as a surprise, but even if the auto_increment is defined as nullable, when you try to insert a null, it just gets the next number

    id,foo
    1,curly
    2,larry
    3,moe
    4,shemp

    okay, so what happens if we don't say auto_increment?
    Code:
    create table nullprimarykey2
    ( id integer primary key 
    , foo varchar(9)
    );
    insert into nullprimarykey2 (foo) 
    values ('curly'),('larry'),('moe');
    Duplicate entry '0' for key 1

    id,foo
    0,curly
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok well that is in application, I am interested in theory why it doesn't work
    Free Science Homework Help
    http://www.physicsforums.com

  8. #8
    SitePoint Addict
    Join Date
    May 2003
    Location
    Auckland
    Posts
    309
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dethfire
    ok well that is in application, I am interested in theory why it doesn't work
    Because those are the rules.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    in theory?

    in theory it doesn't work because a primary key may not be null
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Imminent
    Because those are the rules.
    why is it a rule?

    in theory it doesn't work because a primary key may not be null
    I don't accept that just because someone tells me something that means it's true. I don't doubt your code works and that every database doesn't allow null primary keys. The next question is why have these database makers not allow null primary keys. Database design doesn't have a bible or a god, so what is the reason?
    Free Science Homework Help
    http://www.physicsforums.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    database design does have gods, and their names are date and codd

    spend a few hours over at http://dbdebunk.com/

    i really dislike those guys, but not because they are wrong -- they are most emphatically right

    i'm sure there's plenty of evidence on their site for why primary keys cannot be null
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "null" is simply not appropriate.

    I suspect that you don't fully understand what "null" really means...
    http://msdn.microsoft.com/library/de...qd_02_8pwy.asp
    http://blogs.msdn.com/ericlippert/ar.../30/53120.aspx

    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    hey marcus, can you believe it? i cited dbdebumph!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Um, don't know how to respond... never heard of it! Should I have?!!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  15. #15
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    from msdn:

    "# Null values cannot be used for information required to distinguish one row in a table from another row in a table (for example, foreign or primary keys)."

    But if only one of the keys was null then you could distinguish from the non null keys, right?
    Free Science Homework Help
    http://www.physicsforums.com

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you want to get into it, look at the difference between PRIMARY KEY and UNIQUE index

    a UNIQUE index allows null(s) -- some databases allow one, some multiple

    but consider what the purpose of a PRIMARY KEY is

    it is to allow FOREIGN KEYs to reference it without ambiguity!!

    what is the difference between a FOREIGN KEY that is null because the relationship is optional, versus a FOREIGN KEY that it null because it is "related" to the row with the null PRIMARY KEY (assuming this were allowed)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by M@rco
    Um, don't know how to respond... never heard of it! Should I have?!!
    sorry, must've got you mixed up with someone else

    please check out http://dbdebunk.com/ and let me know what you think
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    what is the difference between a FOREIGN KEY that is null because the relationship is optional, versus a FOREIGN KEY that it null because it is "related" to the row with the null PRIMARY KEY (assuming this were allowed)
    Aha! A good point well put... hadn't given it that much thought... brain not firing on all cylinders at the moment! lol
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  19. #19
    SitePoint Wizard dethfire's Avatar
    Join Date
    Aug 2000
    Posts
    2,477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    if you want to get into it, look at the difference between PRIMARY KEY and UNIQUE index

    a UNIQUE index allows null(s) -- some databases allow one, some multiple

    but consider what the purpose of a PRIMARY KEY is

    it is to allow FOREIGN KEYs to reference it without ambiguity!!

    what is the difference between a FOREIGN KEY that is null because the relationship is optional, versus a FOREIGN KEY that it null because it is "related" to the row with the null PRIMARY KEY (assuming this were allowed)
    ah yes I think that is that answer I'm looking for, that makes sense, the index and key bit, thanks!
    Free Science Homework Help
    http://www.physicsforums.com

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    as a final word (?), let me add that some people have difficulty with an optional relationship, i.e. when a Foreign Key may by null, and i wrote an article about it here: Optional foreign key can be NULL (site registration may be required, but it's free)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    please check out http://dbdebunk.com/ and let me know what you think
    I've had a look, but I don't think much of it, to be honest... do you hold it in particularly high estimation?
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    on the contrary

    they turn me completely off, i have never seen their equal when it comes to stridency, arrogance, egotism, rudeness, and occasional flat out stupidity

    but apparently they have their sh1t together as far as relational theory is concerned

    too bad i can't stomach the way they write about it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937
    sorry, must've got you mixed up with someone else
    Probably MattR


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
  •