Table constraints - PRIMARY KEY

PRIMARY KEY with multiple columns

Let’s say we have a table books with id, name, 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 id and isbn.

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 id and 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 id and isbn as the first record, which violates the primary key constraint.