Sometimes, none of the columns in a table can uniquely identify a record. When this happens, we can designate multiple columns in a table to serve as the primary key, also known as a composite primary key.

For example, say we have a table, books that contains the books sold in a particular week.

The books table has these columns:

• book_title,
• author_name,
• copies_sold

Since an author can have many books and a book can have many authors, there could be repeated listings of a particular book or author in the table. In other words, neither book_title nor author_name can be a unique column.

However, we can derive a composite primary key from the combination of both book_title and author_name.

To designate multiple columns as a composite primary key, use this syntax:

PRIMARY KEY (column_one, column_two)

CREATE TABLE books (
  book_title varchar(50),
  author_name varchar(50),
  copies_sold integer,
  PRIMARY KEY (book_title, author_name)
);