Composite primary key or not?

I personally prefer your 2nd approach (and would use it almost 100% of the time) – introduce a surrogate ID field.

Why?

  • makes life a lot easier for any tables referencing your table – the JOIN conditions are much simpler with just a single ID column (rather than 2, 3, or even more columns that you need to join on, all the time)

  • makes life a lot easier since any table referencing your table only needs to carry a single ID as foreign key field – not several columns from your compound key

  • makes life a lot easier since the database can handle the creation of unique ID column (using INT IDENTITY)

However, I do not know how they
preserve uniqueness of data entries.

Very simple: put a UNIQUE INDEX on the compound columns that you would otherwise use as your primary key!

CREATE UNIQUE INDEX UIX_WhateverNameYouWant 
   ON dbo.ProxUsingDept(fkProx, fkDept)

Now, your table guarantees there will never be a duplicate pair of (fkProx, fkDept) in your table – problem solved!

Leave a Comment