Composite primary key or not?

Solution 1:

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!

Solution 2:

You ask the following questions:

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

Uniqueness can be preserved by declaring a separate composite UNIQUE index on columns that would otherwise form the natural primary key.

Which way is better?

Different people have different opinions, sometimes strongly held. I think you will find that more people use surrogate integer keys (not that that makes it the "right" solution).

What are the bad sides of using the 2nd approach?

Here are some of the disadvantages to using a surrogate key:

1. You require an additional index to maintain the unique-ness of the natural primary key.

2. You sometimes require additional JOINs to when selecting data to get the results you want (this happens when you could satisfy the requirements of the query using only the columns in the composite natural key; in this case you can use the foreign key columns rather than JOINing back to the original table).