I'm designing database which some tables have modifiable PK, I want to know, is design like this good or bad? Is it make programmer hard to code?
3 Answers
Bad. If the PK is regularly changed (not as part of some reogrinanization) then it was not a natural PK or the application design is bad.
-
What does "part of some reogrinanization" means? Could you give me some example?– EkkapopJul 25, 2010 at 2:41
-
Ekkapop: if you have to change the primary keys when you're completely redesigning your database schema, that's expected. If you have to update a primary key because somebody got married, that's bad.– freiheitJul 25, 2010 at 2:43
-
Exactly. Similar would be running out of primary keys and assigning a larger type, for example. Example: Account Numbers - 8 digits, suddenly are 12 digits long. If one uses "real" fields for the PK. This is a reorganinization.– TomTomJul 25, 2010 at 2:54
That sounds hard to code for. You change the primary key for a record, and you have to change all references to that primary key in all tables. Easy to mess up. Just use a sequence (or auto_increment or whatever) and make a primary key that isn't data. Or pick a primary key that's really unlikely to ever need to change.
-
1Use a [surrogate][1] (or artificial or synthetic) key: [1]: en.wikipedia.org/wiki/Surrogate_key– tegbainsJul 25, 2010 at 7:01
It's kind of been said, but (my) best practice is to always name a primary key 'id' and make it a serial (autoincrement integer) (identity integer in MSSQL). It's happened to my on many occasions that I had to change the PK when it was some kind of natural key like someone's name.
-
If there's an actual PK in the data, there's really no need for a surrogate. Jul 25, 2010 at 9:36
ON UPDATE CASCADE
, you relatively safe (although updating such a name might trigger a lot of FK updates).