Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dependence on an Auto-Increment Primary Key

    Guelphdad, this question is especially for you, since I have been following you along and reading your articles, but there's still something I'm not clear on.

    Let's say you have a database for storing articles, and information related to those articles. Let's say I have an "articles" table (two fields, one is an auto PK "id", and the other is a text "content"). Now let's say there is a "links" table that contains links related to a given article (three fields, an auto PK "id", an int "article_id" for the article it refers to, and varchar "link").

    1. This seems to have disaster written all over it. The reason is that these "auto_increment" fields are being referred to from outside the table. Wouldn't this mean that if you had gaps in the fields, made a dump of the table, and re-inserted all the information, suddenly the other tables would be pointing to the wrong articles? The "dump" scenario is only the most obvious, it feels like this could get messed up for plenty of other reasons.

    2. I just want to confirm my understanding of your article, that the best way to run an INSERT into both tables would be (very simplified):

    a. INSERT INTO articles (content) VALUES ("some content");
    b. INSERT INTO links (article_id, link) VALUES (last_insert_id(), 'some link');

    Does this look right?

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    yes that is correct.

    as for the database dump, don't worry. the data will not get changed. when you import back into a database none of the values get changed, even when an auto increment field is being loaded.

    what might throw you off is that when you do not have an auto incremented field in your table and then add one, it gets automatically filled with values.

    that doesn't happen on an import from a dump file though.

  • #3
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So does that mean that data inside an auto_increment field is "safe" as long as you don't start changing it yourself?

    Just to be clear, I plan on not only referring to this PK ID from other tables but also from the outside, such as displaying an "article number", and even having filenames based on them (i.e. "article_1.jpg", etc). Would this be acceptable as long as I make sure that I do not change the PK of the article table myself without changing everything else that refers to it?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    correct, don't change the values in your auto increment column. that is why I (and others) point out the numbers should never be changed when people are worried about gaps in the series of numbers.

    often times you will see people with ids of
    1,2,5,6,7 and they want to go back and insert a value for 4 which got deleted at some point.

    The reason you shouldn't be doing that of course is exactly what you are questioning, a change here can mess up other parts of your application or sub tables etc.

  • #5
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, well I'll definitely be sure not to mess around with them then, but I guess I just want double assurance that they won't get messed up by anything else, beyond my control...

    They won't right??

    Please tell me they won't....

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    They won't mess up if you don't mess them up!

  • #7
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's all I needed to know! Thanks a bunch.


  •  

    Posting Permissions

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