up vote 0 down vote favorite
share [g+] share [fb]

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?

link|improve this question
50% accept rate
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
feedback

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.

link|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
feedback
up vote 2 down vote

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.

link|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
feedback
up vote 0 down vote

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.

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

Your Answer

 
or
required, but never shown
discard

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