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?

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.

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?

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.

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

They won't mess up if you don't mess them up!

That's all I needed to know! Thanks a bunch.