0

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?

1
  • 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, 2010 at 8:51

3 Answers 3

4

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.

3
  • What does "part of some reogrinanization" means? Could you give me some example?
    – Ekkapop
    Jul 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.
    – freiheit
    Jul 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.
    – TomTom
    Jul 25, 2010 at 2:54
2

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.

1
0

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.

1
  • If there's an actual PK in the data, there's really no need for a surrogate. Jul 25, 2010 at 9:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .