nelsonm - 5:24 pm on Aug 26, 2011 (gmt 0)
OMG! Thanks so much for your tip on not using the primary key as an ID for records. This is the third database driven web app i've created. While the first two apps don't use the primary key as an ID, this third app does! I did not think of the problems that would be encountered if the database table ever has to be regenerated.
I'm in the middle of creating a web based customer & job tracking system for a client. The mysql database consists of; customer, employee, pricelist, joblist, workorder tables and so on. They all use the primary key as an ID.
I will add a new ID column for each table right away!
Normally this is not a problem since primary keys do not reuse deleted key numbers. Also, the default for exporting a database or tables in phpMyAdmin is to include the Auto Increment (primary key) values.
So my questions are...
1. is not using the primary key as an ID normal practice?
2. what's the probability of this issue occurring?
I don't remember reading about this issue when learning about MySql or relational databases in general, but it's an obvious point!
Here's "Poster Child" example i had just experienced, i just had a problem with table that used the primary key as an ID. I needed to delete an index entry. While phpMyAdmin said i could - i could not delete it, so i exported the table without the auto increment (primary key) values. When i recreated the table, i realized that all the ID's (primary keys) changed in relation to the records.
So the moral if this story is to export tables with auto increment values. Or, as you pointed out, don't use the primary key as an ID.
I have just read that a primary key is also called an "Artificial" primary key because it is an integer number that is system auto-generated, auto-incremented, and maintained by MySQL database engine. It's also preferred that ID's be "Artificial" integer keys, just not the primary one. But doesn't the problem you described have to do with system auto-generated, auto-incremented keys?
If say... the customer ID is another "Artificial" integer key, doesn't regenerating the table pose the same problem with any system auto-generated, auto-incremented keys?