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

sql

### CREATE TABLE Persons (

### PersonID int NOT NULL,

### LastName varchar(255) NOT NULL,

### PRIMARY KEY (PersonID)
);

A shorthand on one column

sql

### CREATE TABLE Persons (

### PersonID int PRIMARY KEY,

### LastName varchar(255) NOT NULL
);

Composite primary key (multiple columns)

sql

### 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

sql

### ALTER TABLE Persons

### ADD PRIMARY KEY (PersonID);

Drop a primary key

sql

### 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.