Why use multiple columns as primary keys (composite primary key)

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?

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.

Another example of compound primary keys are the usage of Association tables. Suppose you have a person table that contains a set of people and a group table that contains a set of groups. Now you want to create a many to many relationship on person and group. Meaning each person can belong to many groups. Here is what the table structure would look like using a compound primary key.

Create Table Person(
PersonID int Not Null,
FirstName varchar(50),
LastName varchar(50),
Constraint PK_Person PRIMARY KEY (PersonID))

Create Table Group (
GroupId int Not Null,
GroupName varchar(50),
Constraint PK_Group PRIMARY KEY (GroupId))

Create Table GroupMember (
GroupId int Not Null,
PersonId int Not Null,
CONSTRAINT FK_GroupMember_Group FOREIGN KEY (GroupId) References Group(GroupId),
CONSTRAINT FK_GroupMember_Person FOREIGN KEY (PersonId) References Person(PersonId),
CONSTRAINT PK_GroupMember PRIMARY KEY (GroupId, PersonID))

The W3Schools example isn't saying when you should use compound primary keys, and is only giving example syntax using the same example table as for other keys.

Their choice of example is perhaps misleading you by combining a meaningless key (P_Id) and a natural key (LastName). This odd choice of primary key says that the following rows are valid according to the schema and are necessary to uniquely identify a student. Intuitively this doesn't make sense.

1234     Jobs
1234     Gates

Further Reading: The great primary-key debate or just Google meaningless primary keys or even peruse this SO question

FWIW - My 2 cents is to avoid multi-column primary keys and use a single generated id field (surrogate key) as the primary key and add additional (unique) constraints where necessary.

You use a compound key (a key with more than one attribute) whenever you want to ensure the uniqueness of a combination of several attributes. A single attribute key would not achieve the same thing.

Yes, they both form the primary key. Especially in tables where you don't have a surrogate key, it may be necessary to specify multiple attributes as the unique identifier for each record (bad example: a table with both a first name and last name might require the combination of them to be unique).