PRIMARY KEY

PRIMARY KEY

A PRIMARY KEY uniquely identifies each row in a table. It combines NOT NULL + UNIQUE: no empty values, no duplicates.

Create with a column definition

CREATE TABLE Persons (
  PersonID int NOT NULL,
  LastName varchar(255) NOT NULL,
  PRIMARY KEY (PersonID)
);

A shorthand on one column

CREATE TABLE Persons (
  PersonID int PRIMARY KEY,
  LastName varchar(255) NOT NULL
);

Composite primary key (multiple columns)

CREATE TABLE OrderItems (
  OrderID int NOT NULL,
  ProductID int NOT NULL,
  Quantity int,
  PRIMARY KEY (OrderID, ProductID)
);

Here the pair (OrderID, ProductID) must be unique together.

Add a primary key later

ALTER TABLE Persons
ADD PRIMARY KEY (PersonID);

Drop a primary key

ALTER TABLE Persons
DROP PRIMARY KEY;   -- MySQL

Rules

• A table should have only one primary key (it may cover several columns).
• Primary keys are often auto-incremented numbers (e.g. CustomerID).

TL;DR

• PRIMARY KEY uniquely identifies each row.
• It is NOT NULL + UNIQUE.
• Can span multiple columns (composite key).
• One primary key per table.