We have just said that you can't have two primary keys in one table, but your primary key can consist of more than one column. Take a look at the following example:
CREATE TABLE order_item ( order_id integer, sequence integer, name varchar(32), quantity integer, PRIMARY KEY (order_id, sequence) );
We've got a table which keeps information about items in a given order. We keep the order_id for each item. We also keep the sequence number of the item in the purchase.
The column order_id isn't enough as the primary key; many items can be ordered together in one purchase and treated as one order. On the other hand, we can't use the sequence column as the primary key either, as the first purchase starts with item no. 1, followed by no. 2, etc., but the next purchase starts the counting again from 1. Therefore, to obtain unique values for the primary key, we have to take the two columns together.
Primary keys with more than one column are often called composite primary keys.
Movies sometimes have the same title, and we should treat the combination of the movie title + the year of production as the primary key.
Let's recreate the movie table with the following columns:
title – up to 64 characters.year – an integer.genre – up to 10 characters.The first two columns create the primary key.
Type:
CREATE TABLE movie ( title varchar(64), year integer, genre varchar(10), PRIMARY KEY (title, year) );
