Primary Key vs Foreign Key vs Unique Key Explained

Primary key

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255)
);

The primary key is the column, or set of columns, that uniquely identifies each row in the table. A table can have exactly one, it's implicitly NOT NULL, and it's usually the column that other tables reference to point back at a specific row.

Unique key

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    tax_id VARCHAR(20) UNIQUE
);

A unique key enforces the same "no duplicates" rule as a primary key, but a table can have as many unique constraints as it needs — here, both email and tax_id must each be unique independently, even though neither is the primary key.

Foreign key

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

A foreign key doesn't enforce uniqueness on its own column at all — customer_id can (and usually does) repeat across many orders. What it enforces is that every value placed in customer_id must already exist as an id in the customers table, preventing an order from ever pointing at a customer that doesn't exist.

NULL handling in unique keys, by database

One detail catches people off guard: whether a unique column can hold more than one NULL. The ANSI SQL standard says yes — since NULL is never considered equal to another NULL, uniqueness checks don't apply to it, so any number of rows can have NULL in a unique column.

SQL Server is the outlier here: a standard UNIQUE constraint or index rejects a second NULL, even though the ANSI standard permits it. The workaround, if multiple NULLs need to be allowed while still enforcing uniqueness on non-NULL values, is a filtered unique index: CREATE UNIQUE NONCLUSTERED INDEX ... WHERE column IS NOT NULL.

All three together in one schema

CREATE TABLE customers (
    id INT PRIMARY KEY,               -- primary key
    email VARCHAR(255) UNIQUE NOT NULL  -- unique key
);

CREATE TABLE orders (
    id INT PRIMARY KEY,               -- primary key
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id)             -- foreign key
        REFERENCES customers(id)
);

Every real schema leans on all three at once: primary keys give every row an identity, unique keys guard business rules that aren't the primary key (an email address, a tax ID), and foreign keys stitch tables together while guaranteeing the relationship always points at something real.

Comparison table