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