Why does an SQL table have multiple primary keys?

Question

On my job (non programming job that takes advantage of some of my programming skills), I was asked to generate a report from our outsourced, custom made program's SQL database.

When looking at the tables I see that many of them have multiple fields assigned as PK, why is that? What advantage does that have over a serial, numeric id?

Answer 1

Data integrity. A key means a uniqueness constraint that prevents duplicate data entering the database. Keys help ensure that facts in the database can be properly identified with the real-world objects or concepts that they are supposed to describe. In practice not everything can or should be identified by a single attribute in a single column.

Note: A key with more than one attribute is not multiple keys. It's still just one key (called a composite key).

Answer 2

It is called a composite key. If order_no is primary key in table Order and in table Order_Item there is a unique sequence number within order assigned to each order item, i.e. all orders have items beginning with sequence number 1 then the primary key for Order_Item would be order_no, item_sequence_no.

Contrast this with a generated primary key for Order_Item: you would still need a unique index on order_no, item_sequence_no. If you think about the queries you might find that more rely on the composite key.

The business dependent and system generated keys are technically known as domain key and surrogate key respectively. c2 wiki has a good discussion on it.