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');
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?