PRIMARY KEY with multiple columns
Let’s say we have a table
price and an international standard book number (
isbn) as columns, and we want each individual book to be identified uniquely by a combination of their
In these cases, the
PRIMARY KEY can be specified as a combination of multiple columns. Look at the
CREATE TABLE statement below.
To specify the combination of a group of columns as the PRIMARY KEY, simply add
PRIMARY KEY as a new item in the CREATE TABLE list, followed by the list of columns that constitute the PRIMARY KEY.
Observe the results of set of statements below.
Notice that the third insert statement works, even though it has the same
isbn as the first record, and
isbn is a part of the primary key. But it has different
id. Since the primary key is a combination of
isbn, the first and third records are unique.
However, the fourth insert throws a
UNIQUE constraint violation error. That’s because it has the same combination of
isbn as the first record, which violates the primary key constraint.