0

I am currently supplementing my table's natural primary key with a unique, auto-increment ID column.

enter image description here

This makes it easier to compare, update, and delete records from code (simpler WHERE clauses).

How does this compare to using ID as the primary key with a unique key on Category/DisplayName?

Are there any advantages to using one approach over the other?

Dan Bechard
  • 5,104
  • 3
  • 34
  • 51
  • nothing wrong with a composite primary key, until you end up having to list them in every dependent table. linking a single field as a foreign key is FAR simpler than composite foreign keys. – Marc B Aug 21 '14 at 18:48
  • If most of your queries are going to use the new ID column you might consider making that your clustered index also. And since your identity is not the primary key you need to make sure you create a unique constraint on that column too. – Sean Lange Aug 21 '14 at 18:50
  • 1
    There will also be a big difference if your primary key is also your clustered index. The clustered index orders the data, so any inserts would go to the bottom if your ID is the clustered index/primary key. If the composite shown above is the clustered index, any insert could potentially cause a page split, depending on the location and page density. – Dave.Gugg Aug 21 '14 at 18:50
  • 1
    Choosing one option over the other can be a highly contentious debate. Good luck! – Dave Mason Aug 21 '14 at 18:50
  • I think the first answer here http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-application-developers is quite decent as far as basic database design standards go. Your industry and technologies may determine what others around you think is proper. Most Oracle databases I have worked with have an abundance of natural keys. – JoelC Aug 21 '14 at 20:44

0 Answers0