1

If we have a table whose primary key is a Composite Key, do we always create a new column as a primary key instead of the composite key?, or it depends on DB Analysis?

Which way is the best? And why?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ziad Salem
  • 496
  • 13
  • 34
  • No we don't always create an additional column instead of the composite key. It does depend on DB analysis. – Nick.Mc Jan 01 '14 at 13:36
  • 2
    The only design rule that's always followed, is the one where we don't always follow the "rules". – Tony Hopkinson Jan 01 '14 at 13:42
  • Have been discussed quite a few times, few such links: http://stackoverflow.com/questions/4737190/composite-primary-key-or-not?rq=1, http://stackoverflow.com/questions/159087/composite-primary-keys-versus-unique-object-id-field – Vasan Jan 01 '14 at 13:43

3 Answers3

0

Probably the main reason I'd add a surrogate key primary key as well as a unique composite, is if I needed it for a foreign key for another table.

The second would be if I suspected the primary composite key was likely to become not unique in the near future.

Other than that you'd have to be talking some sort of very specific optimisation.

Best is subjective always.

Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
0

This is the classical natural vs surrogate key discussion.

Neither is absolutely "better" - it all depends on what kind of engineering trade-off you are trying to achieve. Fot the list of pros and cons, see here.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

I think you can use the way you want based on your analysis, but if your primary keys (composite) may be repetition you should use a new key as primary. and you should notice foreign keys and another properties making a composite key, if you used a non-key property to make part of your composite key, you would have to be sure from your analysis and then rebuild you keys again.

Mohamed Loay Dahhan
  • 23
  • 4