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

-- Creates the customers table (the referenced side).
CREATE TABLE customers (
    customer_id INT          NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    PRIMARY KEY (customer_id)
);

-- Creates the orders table (the side that holds the foreign key).
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
);

-- Creates a junction table with a composite primary key (many-to-many).
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
);

-- Error example 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 example 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, SQL Server uses IDENTITY, and SQLite uses INTEGER PRIMARY KEY.

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

-- PostgreSQL
order_id SERIAL PRIMARY KEY

-- SQL Server
order_id INT NOT NULL IDENTITY(1,1), PRIMARY KEY (order_id)

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