PRIMARY KEY / FOREIGN KEY

Sets a primary key (inline column definition).

column_name data_type PRIMARY KEY

Sets a primary key (as a table constraint).

PRIMARY KEY (column_name)

Sets a composite primary key.

PRIMARY KEY (column1, column2)

Sets a foreign key.

FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column)
    ON DELETE action
    ON UPDATE action

The following customers and orders tables are used in the examples below.

Creates the customers table (the referenced side).

sample_primary_foreign_key.sql

CREATE TABLE customers (
    customer_id INT          NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id)
);

Query OK, 0 rows affected

Verifies the table structure.

sample_primary_foreign_key.sql

DESCRIBE customers;

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| customer_id | int          | NO   | PRI | NULL    | auto_increment |
| name        | varchar(100) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set

Creates the orders table (the side that holds the foreign key).

sample_primary_foreign_key.sql

CREATE TABLE orders (
    order_id    INT  NOT NULL AUTO_INCREMENT,
    customer_id INT  NOT NULL,
    total       INT  NOT NULL,
    PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Query OK, 0 rows affected

Verifies the table structure. The customer_id column has a foreign key (MUL) set.

sample_primary_foreign_key.sql

DESCRIBE orders;

+-------------+------+------+-----+---------+----------------+
| Field       | Type | Null | Key | Default | Extra          |
+-------------+------+------+-----+---------+----------------+
| order_id    | int  | NO   | PRI | NULL    | auto_increment |
| customer_id | int  | NO   | MUL | NULL    |                |
| total       | int  | NO   |     | NULL    |                |
+-------------+------+------+-----+---------+----------------+
3 rows in set

Creates a junction table with a composite primary key (many-to-many).

Note: The examples below assume the products table has already been created separately.

sample_primary_foreign_key.sql

CREATE TABLE order_products (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders   (order_id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE RESTRICT
);

Query OK, 0 rows affected

Error when an INSERT violates a foreign key constraint (MySQL).

-- ERROR 1452 (23000): Cannot add or update a child row:
-- a foreign key constraint fails (`shop`.`orders`, CONSTRAINT `fk_orders_customer`
-- FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Error when trying to delete a referenced row with ON DELETE RESTRICT.

-- ERROR 1451 (23000): Cannot delete or update a parent row:
-- a foreign key constraint fails

The basic syntax for PRIMARY KEY and FOREIGN KEY is supported across all major databases.

CREATE TABLE orders (
    order_id    INT NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

The syntax for auto-incrementing primary keys varies by database. MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL, and SQLite uses INTEGER PRIMARY KEY.

-- MySQL
order_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (order_id)

-- PostgreSQL
order_id SERIAL PRIMARY KEY

-- SQLite
order_id INTEGER PRIMARY KEY

The ON DELETE and ON UPDATE actions (CASCADE, SET NULL, RESTRICT) are supported across all major databases. Note that SQLite disables foreign key constraints by default — you must enable them with PRAGMA foreign_keys = ON;.

-- SQLite (enable foreign key constraints)
PRAGMA foreign_keys = ON;

A PRIMARY KEY uniquely identifies each row. Integer sequences (AUTO_INCREMENT) are commonly used, but natural keys such as email addresses or product codes can also serve as primary keys. A composite primary key ensures uniqueness through a combination of multiple columns.

When a FOREIGN KEY is defined, any attempt to insert a value into the foreign key column that does not exist in the referenced table will result in an error. This is called referential integrity. Using ON DELETE CASCADE means that deleting a parent record automatically deletes its child records, eliminating the need for manual cleanup.

For the basics of creating tables, see CREATE TABLE. For other column constraints, see NOT NULL / UNIQUE / DEFAULT.