Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

 
discard

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.