database design - Why use multiple columns as primary keys (composite primary key)

Question

This example is taken from w3schools.

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

My understanding is that both columns together (P_Id and LastName) represent a primary key for the table Persons. Is this correct?

• Why would someone want to use multiple columns as primary keys instead of a single column?
• How many columns can be used together as a primary key in a given table?

Answer 1

Your understanding is correct.

You would do this in many cases. One example is in a relationship like OrderHeader and OrderDetail. The PK in OrderHeader might be OrderNumber. The PK in OrderDetail might be OrderNumber AND LineNumber. If it was either of those two, it would not be unique, but the combination of the two is guaranteed unique.

The alternative is to use a generated (non-intelligent) primary key, for example in this case OrderDetailId. But then you would not always see the relationship as easily. Some folks prefer one way; some prefer the other way.