Master

Tables that vouch for each other

I joined two tables and trusted the link numbers to line up. They did, until a stray row pointed at an id that never existed and quietly broke every join. A foreign key is the database enforcing that link.

What you will see

sqlite> Error: FOREIGN KEY constraint failed

About 2 min of reading

Try It

Create a table that points at another and let the database reject a broken link.

The users table already exists with a primary-key id. Draw orders that points at it.

CREATE TABLE orders (num INTEGER, user_id INTEGER REFERENCES users (id));

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Insert an order for a user that exists. The link is valid so it lands.

INSERT INTO orders (num, user_id) VALUES (100, 1);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Insert an order for a user that does not exist. The rule rejects it and the table is untouched.

INSERT INTO orders (num, user_id) VALUES (101, 999);

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite> Error: FOREIGN KEY constraint failed

This is the payoff. Without the rule, that row would sit in the table and every join would carry a phantom user. The rule turns a silent data bug into a loud error at write time.

Data Flow

REFERENCES declares that a column must match another table, and every INSERT is checked against that rule before it lands.

1 REFERENCES names a target

↓

2 INSERT brings a value

↓

3 Database checks the link

↓

4 Bad link is rejected

Why this works

The link is a rule

A foreign key is a column that must match a value in another table. It is how split tables stay connected without drifting apart. Declare it with REFERENCES and the database enforces it on every write, so a join can never meet a row that points nowhere.

sqlite is strict about it

sqlite will not let you create a table whose foreign key points at a table that does not exist yet, and it wants the referenced column to be a primary key. That ordering is a trap: create the table you point at first, then the one that points to it. Plan the order of your CREATE statements and the rest follows.

Examples

Point at another table

CREATE TABLE orders (id INTEGER, user_id INTEGER FOREIGN KEY user_id REFERENCES users id);

That is the intent but not the accepted shape. sqlite keeps the link as a rule you declare, and the wording below is the one it accepts.

The accepted shape

CREATE TABLE orders (id INTEGER, user_id INTEGER REFERENCES users (id));

REFERENCES users (id) says this column may only hold ids that exist in users. The database checks every INSERT against that promise.

Refuse a dangling link

INSERT INTO orders (id, user_id) VALUES (1, 999);

If no user has id 999, this is rejected. The error tells you the link is broken, so the bad row never lands and your joins stay honest.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Making lookups fast

Previous Lesson

Making lookups fast

Next Lesson

A saved question

A saved question →