Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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]: – tegbains Jul 25 '10 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.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.