This lesson's interactive features are locked, please to keep using them

Primary Keys

Up until now, a table's primary key has only been one column. But we need to expand the definition of a primary key to allow for a combination of columns (or a composite key) to be used to identify the records of a table.

In the many-to-many joining table products_suppliers, we used UNIQUE (product_id, supplier_id) to prevent duplicate pairs of IDs. We can instead declare that pair as the table's primary key:

CREATE TABLE products_suppliers (
  product_id INTEGER NOT NULL,
  supplier_id INTEGER NOT NULL,
  PRIMARY KEY (product_id, supplier_id)
);

Neither ID has to be unique on its own, but their combination must be. We explicitly add NOT NULL because SQLite allows NULL values in composite primary keys on ordinary tables.

Candidate Keys

In some tables, it may be possible to use columns other than the primary key to uniquely identify each record. Each possible minimal means of uniquely identifying records is known as a candidate key. The primary key is a candidate key, but there may be others.

When applying database normalization, we have to consider every candidate key.

Take a users table with id and email columns:

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL, full_name TEXT);

Both primary key id and unique email are candidate keys because they can be used to identify each row.

Assignment

Let's give CashPal's users_countries joining table an explicit composite primary key.

Replace the UNIQUE constraint with a composite primary key containing country_id followed by user_id.
Add NOT NULL to both ID columns so every relationship has a country and a user.
Keep the existing foreign keys and the users and countries tables.