Take the 2-minute tour ×

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?

share|improve this question
Maybe more a question for StackOverflow. For example, for tables used for list-of-values, you could use the actual value as PK, instead of introducing a pseudo-ID. With some reference with ON UPDATE CASCADE, you relatively safe (although updating such a name might trigger a lot of FK updates). –  pascal Jul 28 '10 at 8:51
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
What does "part of some reogrinanization" means? Could you give me some example? –  Ekkapop Jul 25 '10 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. –  freiheit Jul 25 '10 at 2:43
Thanks @freiheit, your answer makes me smile. =) –  Ekkapop Jul 25 '10 at 2:46
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. –  TomTom Jul 25 '10 at 2:54
add comment

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.

share|improve this answer
Use a [surrogate][1] (or artificial or synthetic) key: [1]: en.wikipedia.org/wiki/Surrogate_key –  tegbains Jul 25 '10 at 7:01
add comment

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.

share|improve this answer
If there's an actual PK in the data, there's really no need for a surrogate. –  Ben Pilbrow Jul 25 '10 at 9:36
add comment

Your Answer


