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?

Ekkapop
  • 113
  • 1
  • 4
  • 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 08:51

3 Answers3

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.

TomTom
  • 51,649
  • 7
  • 54
  • 136
  • What does "part of some reogrinanization" means? Could you give me some example? – Ekkapop Jul 25 '10 at 02: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 02: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 '10 at 02: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.

freiheit
  • 14,544
  • 1
  • 47
  • 69
  • 1
    Use a [surrogate][1] (or artificial or synthetic) key: [1]: http://en.wikipedia.org/wiki/Surrogate_key – tegbains Jul 25 '10 at 07:01
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.

Halfgaar
  • 8,084
  • 6
  • 45
  • 86