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 keymakes life a lot easier since the database can handle the creation of unique
ID
column (usingINT 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!