FOREIGN KEY Constraint

The FOREIGN KEY constraint links two tables. It makes sure that a value in one table (the child) exists in another table (the parent).

A foreign key usually points to the primary key of the other table.


Syntax

CREATE TABLE child_table (
  column_name datatype REFERENCES parent_table (column)
);

Or for a table that already exists:

ALTER TABLE child_table
ADD CONSTRAINT fk_name FOREIGN KEY (column_name)
REFERENCES parent_table (column);

Example: Orders and Customers

You already know the customers and orders tables from the joins lessons. The orders table was created with a foreign key:

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT REFERENCES customers (customer_id),
  ...
);

So every order's customer_id must be a real customer. Let's try to add an order for customer 99, who does not exist:

INSERT INTO orders
VALUES (108, 99, 'Mouse', 'Pending', '2024-05-01');

Output:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(99) is not present in table "customers".

The insert failed. There is no customer with id 99.

The foreign key also protects the parent table. You cannot delete a customer who still has orders:

DELETE FROM customers WHERE customer_id = 1;

Output:

ERROR:  update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (customer_id)=(1) is still referenced from table "orders".

Alice still has orders, so deleting her would leave orders that point to nobody.


Key Points

  • A foreign key keeps the links between tables correct.
  • It stops orders (or other child rows) that point to nothing.
  • The column it points to must be a PRIMARY KEY or UNIQUE.

Next, you will learn CHECK, which lets you write your own rule for a column.

Answer the question below to check how foreign keys work.