Primary Keys

Primary keys are special columns that are used to uniquely identify each row of a table in SQL.

CREATE TABLE table_key (
  id INTEGER PRIMARY KEY,
  column_1 TEXT,
  column_2 INTEGER
);

The PRIMARY KEY constraint is used to create columns that uniquely identify each row. A primary key column has a few requirements:

• None of the values can be NULL.
• Each value must be unique (e.g., two rows in a customers table wouldn’t have the same primary customer_id).
• A table cannot have more than one primary key.

Attempts to insert a row with an existing primary key will result in a constraint violation that prevents the new row from being added.

If a table was created without a primary key, it can be added with the ALTER TABLE command. The statement below adds a primary id column, via the PRIMARY KEY constraint, to table_name:

ALTER TABLE table_name
ADD PRIMARY KEY (id);

When the primary key for one table appears in a different table, it is called a foreign key. The most common types of joins will be joining a foreign key from one table with the primary key from another table.

For instance, the following orders table is created and joined via FOREIGN KEY with an existing customer table through its customer_id:

CREATE TABLE orders (
  order_id INTEGER NOT NULL,
  total_cost FLOAT
  purchase_date DATE
  PRIMARY KEY (order_id)
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

The displayed orders table, with its primary key (order_id) and foreign key (customer_id), may look like this:

Sometimes, having one primary key per table is not enough to uniquely identify a row. In such cases, multiple columns would work as composite keys for the table. This requirement should be detected during the designing phase of a database.

For example, a database of car parts will have to uniquely identify a row of parts. Either the engine_ID or body_ID could be used. However, this may create ambiguity as cars could get their engines swapped.

Depending on local regulations, a car may require an engine part ID and a body ID to be associated with a license plate. One solution might be adding more row information about the car, such as left_door_ID, gearbox_ID, etc. But then a specific car would have to be identified by two different aspects: its body and its engine.

A composite key would be useful in this case. This is how a vehicle_registry table might look (extra parts/columns omitted for brevity):

The statement below creates the vehicle_registry table with a composite key:

CREATE TABLE vehicle_registry (
  engine_id INTEGER,
  body_id TEXT,
  gearbox_id INTEGER,
  purchase_date DATE,
  PRIMARY KEY(engine_id, body_id, purchase_date)
);