Composite primary key or not?

Question

Here's what's confusing me. I often have composite primary keys in database tables. The bad side of that approach is that I have pretty extra work when I delete or edit entries. However, I feel that this approach is in the spirit of database design.

On the other side, there are friends of mine, who never use composite keys, but rather introduce another 'id' column in a table, and all other keys are just FKs. They have much less work while coding delete and edit procedures. However, I do not know how they preserve uniqueness of data entries.

For example:
Way 1

create table ProxUsingDept (
    fkProx int references Prox(ProxID) NOT NULL,    
    fkDept int references Department(DeptID) NOT NULL,    
    Value int,    
    PRIMARY KEY(fkProx,fkDept)
)

Way 2

create table ProxUsingDept (
        ID int NOT NULL IDENTITY PRIMARY KEY
        fkProx int references Prox(ProxID) NOT NULL,    
        fkDept int references Department(DeptID) NOT NULL,    
        Value int
)

Which way is better? What are the bad sides of using the 2nd approach? Any suggestions?

Answer 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!

Answer 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).

Answer 3

There are cases like M:N join tables where composite keys make most sense (and if the nature or the M:N link changes, you'll have to rework this table anyway).

Answer 4

I know it is a very long time since this post was made. But I had to come across a similar situation regarding the composite key so I am posting my thoughts.

Let's say we have two tables T1 and T2.

T1 has the columns C1 and C2.

T2 has the columns C1, C2 and C3

C1 and C2 are the composite primary keys for the table T1 and foreign keys for the table T2.

Let's assume we used a surrogate key for the Table T1 (T1_ID) and used that as a Foreign Key in table T2, if the values of C1 and C2 of the Table T1 changes, it is additional work to enforce the referential ingegrity constraint on the table T2 as we are looking only at the surrogate key from Table T1 whose value didn't change in Table T1. This could be one issue with second approach.