Foreign keys: links between tables

In short: A foreign key (REFERENCES) protects the link between two tables, and ON DELETE says what happens to linked rows on delete: CASCADE, SET NULL or RESTRICT.

Problem

Delete my account

Maria Ivanova writes: “Please delete my account.” You try:

SQLdatabase: shop

DELETE FROM customers WHERE id = 1;

DELETE FROM customers WHERE id = 1;

▶ RunRestore the queryFresh database

The database refuses. Maria has 12 orders, and each of them points to her. If the customer disappears, the orders will point to no one.

What should happen to her orders? And to her reviews? And to her list of favourite products? The answer is different for each table. And it is written in the schema.

The idea

REFERENCES

You already know the foreign key: a column that points to a row in another table. In CREATE TABLE it is written with REFERENCES table(column). That is how the shop's orders are made:

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *
• statustext *

Here is a new table: each customer's favourite products. The first row goes in, the second points to customer 99, who does not exist:

SQLdatabase: shop

CREATE TABLE wishlist (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  PRIMARY KEY (customer_id, product_id)
);
INSERT INTO wishlist VALUES (1, 17);
INSERT INTO wishlist VALUES (99, 17);

CREATE TABLE wishlist ( customer_id INTEGER NOT NULL REFERENCES customers(id), product_id INTEGER NOT NULL REFERENCES products(id), PRIMARY KEY (customer_id, product_id) ); INSERT INTO wishlist VALUES (1, 17); INSERT INTO wishlist VALUES (99, 17);

▶ RunRestore the queryFresh database

Now the database guards the links itself. A row that points to a customer or a product that does not exist does not get in.

Try it

The favourite products

The table wishlist already has its columns. Make customer_id point to customers and product_id to products.

SQLdatabase: shop

CREATE TABLE wishlist (
  customer_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  PRIMARY KEY (customer_id, product_id)
);

CREATE TABLE wishlist ( customer_id INTEGER NOT NULL, product_id INTEGER NOT NULL, PRIMARY KEY (customer_id, product_id) );

Check▶ Run

The idea

ON DELETE CASCADE

After REFERENCES you can say what should happen when the row you point to is deleted. That is ON DELETE.

CASCADE means “delete me too”. Yavor Penev (customer 20) has two favourite products. Teodora Genova (customer 19) has one. Yavor deletes his account:

SQLdatabase: shop

DELETE FROM customers WHERE id = 20;
SELECT * FROM wishlist;

DELETE FROM customers WHERE id = 20; SELECT * FROM wishlist;

▶ RunRestore the queryFresh database

Yavor's favourites are gone with him. Teodora's are still there. A list of favourites makes no sense without the customer, so CASCADE is right here.

The idea

SET NULL and RESTRICT

SET NULL means “keep me, but without the link”. Questions about products are useful to other buyers too, even when the author has left:

SQLdatabase: shop

DELETE FROM customers WHERE id = 20;
SELECT * FROM questions;

DELETE FROM customers WHERE id = 20; SELECT * FROM questions;

▶ RunRestore the queryFresh database

The question stayed, and customer_id became NULL: a question from a former customer.

RESTRICT means “do not allow the deletion”. Invoices have to be kept for years by law:

SQLdatabase: shop

DELETE FROM customers WHERE id = 20;

DELETE FROM customers WHERE id = 20;

▶ RunRestore the queryFresh database

Without ON DELETE the behaviour is almost the same: the deletion stops. That is why Maria could not delete her account. The orders are the shop's history and must not disappear.

More precisely, if you are curious

Without ON DELETE the rule is NO ACTION. The only difference from RESTRICT is when it is checked: RESTRICT stops at once, while NO ACTION checks at the end of the statement (or of the transaction, if the check is deferred). There is also ON UPDATE for changing the key, but it is rarely used, because a primary key almost never changes. ON DELETE SET DEFAULT is rare too.

See it

SET NULL against NOT NULL

Someone wrote the questions table like this:

customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE SET NULL

Yavor deletes his account again:

SQLdatabase: shop

DELETE FROM customers WHERE id = 20;

DELETE FROM customers WHERE id = 20;

▶ RunRestore the queryFresh database

SET NULL wants to store NULL, and NOT NULL does not allow it. The deletion stops. SQLite does not warn you about this at CREATE TABLE, only when someone tries to delete. A column with SET NULL must be allowed to be empty.

Try it

The delivery addresses

The table addresses keeps the customers' addresses. When a customer deletes their account, their addresses must disappear with them. Fix customer_id.

SQLdatabase: shop

CREATE TABLE addresses (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  city TEXT NOT NULL,
  street TEXT NOT NULL
);

CREATE TABLE addresses ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), city TEXT NOT NULL, street TEXT NOT NULL );

Check▶ Run

The idea

The check has to be on

In the course the database checks foreign keys. You ask it with PRAGMA, SQLite's command for its settings:

SQLdatabase: shop

PRAGMA foreign_keys;

PRAGMA foreign_keys;

▶ RunRestore the queryFresh database

1 means “on”. If you switch it off, REFERENCES no longer guards anything:

SQLdatabase: shop

PRAGMA foreign_keys = OFF;
INSERT INTO orders (customer_id, ordered_at, status) VALUES (99, '2026-10-25', 'new');
SELECT id, customer_id, status FROM orders WHERE customer_id = 99;

PRAGMA foreign_keys = OFF; INSERT INTO orders (customer_id, ordered_at, status) VALUES (99, '2026-10-25', 'new'); SELECT id, customer_id, status FROM orders WHERE customer_id = 99;

▶ RunRestore the queryFresh database

An order for customer 99, who does not exist, went in without an error.

More precisely, if you are curious

In real SQLite the check is off by default. Run sqlite3 on your computer and type PRAGMA foreign_keys;: the answer is 0. The reason is old programs written before SQLite checked foreign keys. The setting applies to one connection to the database only. That is why a program runs PRAGMA foreign_keys = ON; every time it opens the database. This page does it for you. PostgreSQL, MySQL (with InnoDB) and SQL Server check foreign keys by default.

Exercise

Questions about products

The table questions has id, product_id, customer_id and body. When a product is removed from the shop, the questions about it disappear. When a customer deletes their account, their questions stay, but without an author. Add the links.

SQLdatabase: shop

CREATE TABLE questions (
  id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  customer_id INTEGER,
  body TEXT NOT NULL
);

CREATE TABLE questions ( id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, customer_id INTEGER, body TEXT NOT NULL );

Check▶ Run

Test

Check yourself

1. The table orders has customer_id REFERENCES customers(id) with no ON DELETE. What happens on DELETE of a customer who has orders?

The orders’ customer_id becomes NULLThe orders are deleted with themAn error: the customer is not deleted

2. A customer deletes their account. What fits their list of favourite products?

ON DELETE SET NULLON DELETE CASCADEON DELETE RESTRICT

3. The column is customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE SET NULL. What happens when the customer is deleted?

A NOT NULL error, and the customer is not deletedcustomer_id becomes NULLThe row is deleted

4. You run a script in real SQLite, and an INSERT pointing to a customer who does not exist goes through. Why?

Foreign key checking is off by defaultBecause the customer will appear laterREFERENCES does not work in SQLite

Check

Start the lesson over

Related lessons

• Builds on: INSERT: new rows · DELETE: deleting rows · CREATE TABLE: a new table · Constraints: rules for the data
• Continues in: ALTER TABLE: changing a table · Relationship types: 1:1, 1:N, M:N · ER diagram: from a description to a schema · Normalization
• Tasks on this topic: Tables and rules