A primary key names every row so you can find exactly one of it. A foreign key points a row in one table at that named row in another. Together they are the two ideas that make a database relational.
These two constraints are often taught side by side, and for good reason: one cannot do its job without the other. A primary key gives each row a stable, unique identity inside its own table. A foreign key borrows that identity to build a trustworthy link between tables, so that a row can say "I belong to that customer" and the database will guarantee that customer actually exists. This guide defines both precisely, puts them in a clear side by side comparison, walks through a real two table example with sample data, and then covers the parts people trip over: composite keys, natural versus surrogate keys, the ON DELETE and ON UPDATE actions, what an engine does on a violation, and how keys are indexed.
What this guide covers
Every major engine implements these two keys with nearly identical standard SQL, so the concepts transfer wherever you work. The reference pages are worth keeping open: the PostgreSQL constraints documentation, the MySQL foreign key documentation, and the general concept page on foreign keys. If keys are new to you, our broader guide to SQL constraints gives the full picture.
1. What a primary key is
A primary key is the column, or set of columns, that uniquely identifies each row in a table. It bundles three promises into a single declaration:
- Uniqueness. No two rows may share the same key value. Ask for row with
id = 42and you get exactly one row or none, never two. - Not null. A primary key column can never be empty. A row without an identity would be unaddressable, so the engine forbids it.
- One per table. A table has at most one primary key. You can have many other unique constraints, but only one key is the official identity that other tables reference.
Declaring a primary key is a single clause. The engine then enforces those three promises on every write, forever.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);
-- Or add it to an existing table with an explicit name
ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (id);Behind the scenes the key does more than reject duplicates. It is the anchor other tables point at, and, as the indexing section explains, it is almost always backed by an index that makes lookups by id extremely fast.
2. What a foreign key is
A foreign key is a column, or set of columns, in one table whose values must match a primary key (or a unique key) in another table. It declares and enforces referential integrity: the rule that a child row can never reference a parent that does not exist. If orders.customer_id is a foreign key to customers.id, then every order must belong to a real customer. The engine checks this on every insert and update, and it also guards the parent side so you cannot delete a customer out from under their orders.
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Unlike a primary key, a foreign key is not unique and there is no limit on how many you can have. A single row can carry several foreign keys, one to a customer, one to a shipping address, one to a currency. A foreign key value may also repeat freely, because many orders naturally point at the same customer. It may even be NULL if the column allows it, which is how you model an optional relationship.
3. Primary key vs foreign key, side by side
The two keys are complementary, not competing. This table lines them up on the dimensions that matter most in practice.
| Property | Primary key | Foreign key |
|---|---|---|
| Purpose | Uniquely identify a row in its own table | Link a row to a row in another table |
| Uniqueness | Always unique across the table | Not unique; values may repeat |
| Null allowed | Never; must always have a value | Allowed, if the column is nullable (optional link) |
| How many per table | Exactly one | Many; no fixed limit |
| References | Nothing; it is the target | A primary key or unique key in a parent table |
| Indexed by default | Yes, automatically | Only some engines auto index it; often manual |
The one line summary: a primary key answers "which row is this?" and a foreign key answers "which row does this one belong to?". The foreign key's value is a copy of some other row's primary key.
4. A worked example: customers and orders
Nothing makes this concrete like real tables. We will build two: customers, where id is the primary key, and orders, where id is the primary key and customer_id is a foreign key back to a customer.
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
city VARCHAR(80) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Now load a little data. Here is the customers table, with id as its primary key.
| id (PK) | name | city |
|---|---|---|
| 1 | Ada Lovelace | London |
| 2 | Grace Hopper | New York |
| 3 | Alan Turing | Manchester |
And here is orders. The customer_id column is the foreign key: every value in it is a copy of some customers.id. Notice that customer 1 appears twice, which is perfectly legal for a foreign key.
| id (PK) | customer_id (FK) | order_date | total |
|---|---|---|---|
| 101 | 1 | 2026-01-14 | 49.90 |
| 102 | 2 | 2026-01-15 | 128.00 |
| 103 | 1 | 2026-02-02 | 17.50 |
Because the relationship is enforced, a simple join reunites each order with its owner, and the engine guarantees there are no dangling links to clean up first.
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;id | name | total
101 | Ada Lovelace | 49.90
102 | Grace Hopper | 128.00
103 | Ada Lovelace | 17.50Try to insert an order for a customer that does not exist and the write is refused. This is referential integrity doing its job.
-- There is no customer with id 999
INSERT INTO orders (id, customer_id, order_date, total)
VALUES (104, 999, '2026-03-01', 12.00);
-- ERROR: foreign key constraint fails. Nothing is inserted.5. Composite keys
A key does not have to be a single column. A composite key is a primary key made of two or more columns that are unique only when taken together. Join tables, which connect two entities in a many to many relationship, are the classic case. Suppose a student can enroll in many courses and a course can hold many students: the identity of an enrollment is the pair of ids, not either one alone.
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);Here student_id may repeat, course_id may repeat, but the combination may appear only once, so no student can be enrolled in the same course twice. Two things follow. First, a foreign key that points at a composite primary key must itself list the same set of columns, in order. Second, only the whole pair is null free and unique; the individual columns are not required to be unique on their own. Composite keys are natural in modeling but they make foreign keys wordier, which is one reason many teams add a single surrogate id instead, the subject of the next section.
6. Natural vs surrogate keys
When you choose a primary key you pick from two families. A natural key is a value that already carries business meaning and is unique in the real world, such as an email address, an ISBN, or a country code. A surrogate key is a synthetic value with no meaning outside the database, usually an auto generated integer or a UUID. Both are valid; the trade offs decide which fits.
| Aspect | Natural key (e.g. email, ISBN) | Surrogate key (e.g. auto id, UUID) |
|---|---|---|
| Meaning | Has real world meaning; readable | Meaningless; only an internal handle |
| Stability | Can change (people change email); risky | Never changes once assigned; very stable |
| Width and joins | Often wide text; heavier to index and join | Compact integer; fast to index and join |
| Uniqueness guarantee | Depends on the real world staying unique | Guaranteed by the generator |
| Extra column | None needed; reuses existing data | Adds a column with no business value |
| Best when | The value is truly permanent and standard | Almost always the safe default |
The prevailing advice is to reach for a surrogate key by default and keep any natural value as a separate UNIQUE constraint. That way the natural value is still enforced as unique, but the primary key stays stable and narrow, so foreign keys everywhere else stay small and fast. Generating a surrogate is a one line difference between engines.
-- Surrogate key, portable standard form
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEYChoosing keys well is a core skill of schema design. Our guides on how to design a relational database and database normalization show how key choices ripple through the whole model.
7. ON DELETE and ON UPDATE actions
By default, the database protects a parent row: you cannot delete a customer while orders still reference it, and you cannot change that customer's id out from under those orders. But sometimes you want a different behavior, and the foreign key lets you specify it with referential actions. These fire when the referenced parent key is deleted (ON DELETE) or changed (ON UPDATE).
| Action | What happens to the child rows | Typical use |
|---|---|---|
RESTRICT / NO ACTION | Block the operation while any child still references the parent | Safe default; force a human to resolve dependents first |
CASCADE | Delete the children too, or update their key to follow | Child cannot exist without parent (order line items) |
SET NULL | Set the child foreign key to NULL (column must be nullable) | The link is optional (employee's deleted department) |
SET DEFAULT | Set the child foreign key back to its declared default | Reassign orphans to a placeholder parent |
You declare the actions on the foreign key itself. Here, deleting a customer removes their orders, and changing a customer id carries through to the orders automatically.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;Compare that with an optional link, where losing the parent should simply detach the child rather than erase it:
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE SET NULL;Watch out: ON DELETE CASCADE is powerful and quiet. Deleting one parent can silently remove thousands of child rows across several tables, and cascades can chain from table to table. Confirm the blast radius before enabling it in production, and always index the foreign key columns so cascades stay fast.
8. What happens on a violation
Both keys are enforced atomically inside the current transaction, and the rule is the same for each: if a statement would break the constraint, the engine rejects the whole statement and raises an error. Nothing is half written. The three ways to trip a key are worth naming so you recognize the error messages.
Primary key duplicate
Inserting a row whose key already exists fails with a duplicate key or unique violation. The existing row is untouched and the new one is refused.
Primary key null
Inserting a row with no value for the key fails, because a primary key is implicitly NOT NULL.
Foreign key with no parent
Inserting or updating a child so its foreign key points at a missing parent fails, and deleting or updating a parent that still has children fails unless a referential action says otherwise.
-- Duplicate primary key
INSERT INTO customers (id, name, city)
VALUES (1, 'Someone Else', 'Berlin');
-- ERROR: duplicate key value violates unique constraint. Rejected.Because the rejection is atomic, the constraint is also the correct tool for concurrency. Two sessions racing to insert the same key cannot both win: the database serializes the check, so exactly one succeeds and the other gets the violation. That is why a key is safer than a "check first, then insert" pattern in application code, which is open to a race condition. For the wider family of these rules, see SQL constraints explained and the reference on DDL commands.
9. Indexing of keys
Keys and indexes are related but not the same thing. An index is a lookup structure that makes finding rows fast; a key is a rule about identity and references. The connection is that enforcing a key efficiently requires an index, so engines create one for the primary key automatically. Foreign keys are where the surprise lives.
- Primary key: every major engine backs it with a unique index automatically. Lookups and joins by primary key are as fast as the database gets.
- Foreign key: the reference is always checked, but the child column is not always indexed for you. MySQL with InnoDB does create one automatically; PostgreSQL and SQL Server do not. On those engines you should add the index yourself.
-- Index the foreign key column explicitly (PostgreSQL, SQL Server)
CREATE INDEX ix_orders_customer_id
ON orders (customer_id);The payoff shows up in two places: joins from child to parent, and the parent side of a delete or update. Without the index, deleting a customer forces a full scan of orders to find the children, which turns a routine delete into a slow one on a large table. The chart below sketches how much an index on the foreign key column tends to help each of these operations.
The bars are a representative illustration rather than a benchmark: an index on the foreign key greatly speeds reads and cascades, at a small write cost because the index must be maintained on every insert. For most transactional tables that trade is clearly worth it. To go deeper on when and how to add them, read our guide to database normalization explained, and if you want a structured path through all of this from the start, the SQL beginner course builds these habits in order.
Primary keys and foreign keys are the smallest pieces of SQL with the largest payoff. Give every table a stable identity, link tables through that identity, choose your referential actions on purpose, and index the columns that carry the load. Do those four things and your data stays correct and fast no matter who writes to it.