Keys and Relationships Between Tables in SQL

The command DELETE FROM users WHERE id = 3; runs without a single error – yet Carol’s messages on the forum still point to a user with id = 3 who no longer exists. Without a foreign key the database does not know that rows of two tables are related, so it does nothing to stop you from breaking that link.

A database key is a column or a set of columns that either identifies a row uniquely (primary and unique keys) or links it to a row in another table (foreign key). Keys work as constraints: the DBMS itself rejects queries that break uniqueness or refer to rows that do not exist.

Types of Keys in SQL

In SQL you declare three kinds of keys: a primary key (PRIMARY KEY) identifies a row uniquely, a unique key (UNIQUE) forbids repeated values, and a foreign key (FOREIGN KEY) links a row to another table. The other names – candidate, natural, surrogate, composite – describe how a key was chosen and what it consists of.

The examples use the forum practice database from the lesson Relational Databases and DBMS: users, their profiles, threads, messages and tags. The complete creation script is in the section The Forum Practice Database. The syntax targets MySQL 8 with the InnoDB engine.

Primary Key (PRIMARY KEY)

A primary key (PK) is a column or a set of columns whose value is unique for every row and is never NULL. A table can have only one primary key. MySQL builds an index on it automatically, and InnoDB also physically stores the rows in primary key order.

You can declare the key in the column definition or as a separate line at the end of CREATE TABLE. The second form is required for a composite key:

CREATE TABLE users (
    id       INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50)  NOT NULL,
    email    VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

AUTO_INCREMENT is MySQL syntax: the database assigns the next number to every new row (PostgreSQL uses GENERATED ALWAYS AS IDENTITY for this). The other column attributes are covered in a separate lesson of this module about the CREATE statement.

Natural vs Surrogate Key

A natural key, such as an email, can be the primary key, or you can add a surrogate id. Java applications almost always use a surrogate key.

AUTO_INCREMENT numbers are not guaranteed to be consecutive: a failed insert or a rolled-back transaction can “use up” a value. Do not build logic on the assumption that id = 4 always follows id = 3.

Composite Primary Key

A composite key consists of several columns, and the combination has to be unique, not each column on its own. The typical case is a junction table for a many-to-many relationship: one thread can carry a given tag no more than once.

CREATE TABLE topic_tags (
    topic_id INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (topic_id, tag_id)
);

The pairs (2, 1) and (2, 2) are allowed, but inserting (2, 2) a second time is rejected:

INSERT INTO topic_tags (topic_id, tag_id) VALUES (2, 2);

ERROR 1062 (23000): Duplicate entry '2-2' for key 'topic_tags.PRIMARY'

Unique Key (UNIQUE)

A unique key (UNIQUE) is a constraint that forbids repeated values in a column or a set of columns. Unlike a primary key, a table can have as many of them as you need, and a UNIQUE column can contain NULL. It protects alternate keys: a user’s login and email.

CREATE TABLE users (
    id       INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50)  NOT NULL UNIQUE,
    email    VARCHAR(100) NOT NULL UNIQUE,
    phone    VARCHAR(20)  NULL,
    PRIMARY KEY (id),
    CONSTRAINT uq_users_phone UNIQUE (phone)
);

The database will not accept a second user named Bob, even with a different email:

INSERT INTO users (username, email) VALUES ('Bob', '[email protected]');

ERROR 1062 (23000): Duplicate entry 'Bob' for key 'users.username'

The clause CONSTRAINT uq_users_phone UNIQUE (phone) gives the constraint a name. The name appears in error messages and is used to drop the constraint: ALTER TABLE users DROP INDEX uq_users_phone;. If the table was created without this constraint, add it like this:

ALTER TABLE users ADD CONSTRAINT uq_users_phone UNIQUE (phone);

If the column already contains duplicates, the command fails with the same error 1062 – remove the duplicates first.

UNIQUE vs PRIMARY KEY

UNIQUE and NULL

In MySQL a UNIQUE column can hold any number of NULL values: by the SQL standard two unknown values are not considered equal. A phone number is optional for our users, so inserting two users without a phone works, while inserting two users with the same number does not.

INSERT INTO users (username, email, phone) VALUES
    ('Dave', '[email protected]', NULL),
    ('Eve',  '[email protected]',  NULL);
-- Query OK, 2 rows affected

PostgreSQL and Oracle behave the same way. SQL Server allows only one NULL in a unique column – a common surprise when you move a schema between systems.

Foreign Key (FOREIGN KEY)

A foreign key (FK) is a column whose value must match the value of the primary (or unique) key of a row in another table. The table that holds the foreign key is called the child table, and the table it refers to is the parent table. The DBMS checks the link on every insert, update and delete and does not let you refer to a row that does not exist.

Every forum thread has an author. The column topics.author_id refers to users.id:

CREATE TABLE topics (
    id        INT NOT NULL AUTO_INCREMENT,
    title     VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_topics_author FOREIGN KEY (author_id)
        REFERENCES users (id)
);

Now you cannot create a thread with a nonexistent author:

INSERT INTO topics (title, author_id) VALUES ('New thread', 99);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`forum`.`topics`, CONSTRAINT `fk_topics_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`))

And you cannot delete a user who has threads – otherwise the threads would be left without an author:

DELETE FROM users WHERE id = 2;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`forum`.`topics`, CONSTRAINT `fk_topics_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`))

To add a foreign key to an existing table, use ALTER TABLE:

ALTER TABLE topics
    ADD CONSTRAINT fk_topics_author FOREIGN KEY (author_id) REFERENCES users (id);

If the table already has rows that refer to nonexistent users, the command fails: fix the data first.

What MySQL Requires from a Foreign Key

• Both tables must use the InnoDB engine. MyISAM accepts the FOREIGN KEY syntax but silently ignores it.
• The column types must match: INT refers to INT, not to BIGINT or INT UNSIGNED. For strings, the character set and collation must match too.
• A foreign key refers to a PRIMARY KEY or a UNIQUE key of the parent table. Older versions of InnoDB also accepted a reference to an ordinary, non-unique index; since MySQL 8.4 this is rejected by default (the restrict_fk_on_non_standard_key variable is ON).
• The parent table is created before the child table.

MySQL creates an index on the foreign key column itself if a suitable one does not exist yet. That is why queries such as “all threads of a user” (WHERE author_id = 2) do not scan the whole table.

ON DELETE and ON UPDATE: What Happens to Related Rows

The ON DELETE and ON UPDATE clauses define what to do with child rows when the parent row is deleted or its key is changed. By default MySQL rejects the operation while the row is still referenced. That is exactly what happened above when we tried to delete a user who had threads.

The messages table has two foreign keys with different rules. If a thread is deleted, its messages are deleted. If an author is deleted, the messages stay and author_id becomes NULL:

CREATE TABLE messages (
    id        INT NOT NULL AUTO_INCREMENT,
    topic_id  INT NOT NULL,
    author_id INT NULL,
    body      TEXT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_messages_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_messages_author FOREIGN KEY (author_id)
        REFERENCES users (id) ON DELETE SET NULL
);

ON UPDATE CASCADE is rarely needed: a surrogate id never changes. It is useful when a foreign key refers to a natural key, for example a country code.

Table Relationships: One-to-One, One-to-Many, Many-to-Many

There are three kinds of relationships between tables: one-to-one (1:1), one-to-many (1:M) and many-to-many (M:N). The first two are built with a single foreign key, while the third needs a separate junction table with two foreign keys.

One-to-One (1:1)

Each row of one table matches at most one row of the other. A one-to-one relationship is used to move out rarely needed or optional data: we keep the full name and the birth date in a profile rather than in users. The column user_id is both the primary key and a foreign key, so a user cannot have a second profile.

CREATE TABLE user_profiles (
    user_id    INT NOT NULL,
    full_name  VARCHAR(100),
    birth_date DATE,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE CASCADE
);

INSERT INTO user_profiles (user_id, full_name) VALUES (1, 'Another profile');

ERROR 1062 (23000): Duplicate entry '1' for key 'user_profiles.PRIMARY'

If the profile needs its own id, keep the one-to-one relationship another way: user_id INT NOT NULL UNIQUE plus a foreign key. Without UNIQUE you would get a one-to-many relationship.

One-to-Many (1:M)

One row of the parent table matches zero, one or many rows of the child table. This is the most common relationship: a user has many threads, a thread has many messages. The foreign key always goes on the “many” side – into topics.author_id, not into users: otherwise one cell of users would have to store a list of threads, which breaks atomicity (see the module lesson on database normalization). The topics table is shown in the section Foreign Key.

Many-to-Many (M:N)

A thread can have several tags, and one tag is attached to many threads. A foreign key in topics or tags cannot express this, so you create a junction table: each of its rows is one “thread – tag” pair.

CREATE TABLE tags (
    id   INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE topic_tags (
    topic_id INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (topic_id, tag_id),
    CONSTRAINT fk_topic_tags_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_topic_tags_tag FOREIGN KEY (tag_id)
        REFERENCES tags (id) ON DELETE CASCADE
);

The composite primary key does not let you attach a tag to a thread twice, and the foreign keys do not let you refer to a nonexistent thread or tag. The tags of a thread are fetched by a query with two JOINs, which are covered in a separate lesson of this module:

SELECT t.title, g.name
FROM topics t
JOIN topic_tags tt ON tt.topic_id = t.id
JOIN tags g ON g.id = tt.tag_id
WHERE t.id = 2
ORDER BY g.id;

+---------+----------+
| title   | name     |
+---------+----------+
| Cycling | leisure  |
| Cycling | sport    |
| Cycling | shopping |
+---------+----------+

In JPA this relationship is described by @ManyToMany with @JoinTable, and Hibernate creates the same junction table.

The Forum Practice Database

Below is the complete script of the forum database with all the keys and relationships of this lesson. The following lessons of the module use it too: run it in full in the mysql client so that the examples work without changes. The tables are created in dependency order: parent tables first, child tables after.

DROP DATABASE IF EXISTS forum;
CREATE DATABASE forum;
USE forum;

CREATE TABLE users (
    id       INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50)  NOT NULL UNIQUE,
    email    VARCHAR(100) NOT NULL UNIQUE,
    phone    VARCHAR(20)  NULL,
    PRIMARY KEY (id),
    CONSTRAINT uq_users_phone UNIQUE (phone)
);

CREATE TABLE user_profiles (
    user_id    INT NOT NULL,
    full_name  VARCHAR(100),
    birth_date DATE,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE topics (
    id        INT NOT NULL AUTO_INCREMENT,
    title     VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_topics_author FOREIGN KEY (author_id)
        REFERENCES users (id)
);

CREATE TABLE messages (
    id        INT NOT NULL AUTO_INCREMENT,
    topic_id  INT NOT NULL,
    author_id INT NULL,
    body      TEXT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_messages_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_messages_author FOREIGN KEY (author_id)
        REFERENCES users (id) ON DELETE SET NULL
);

CREATE TABLE tags (
    id   INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE topic_tags (
    topic_id INT NOT NULL,
    tag_id   INT NOT NULL,
    PRIMARY KEY (topic_id, tag_id),
    CONSTRAINT fk_topic_tags_topic FOREIGN KEY (topic_id)
        REFERENCES topics (id) ON DELETE CASCADE,
    CONSTRAINT fk_topic_tags_tag FOREIGN KEY (tag_id)
        REFERENCES tags (id) ON DELETE CASCADE
);

INSERT INTO users (username, email) VALUES
    ('Alice', '[email protected]'),
    ('Bob',   '[email protected]'),
    ('Carol', '[email protected]');

INSERT INTO user_profiles (user_id, full_name) VALUES
    (1, 'Alice Johnson');

INSERT INTO topics (title, author_id) VALUES
    ('Fishing', 1),
    ('Cycling', 2);

INSERT INTO messages (topic_id, author_id, body) VALUES
    (1, 1, 'Where is the fish biting in November?'),
    (1, 3, 'At the reservoir, early in the morning.'),
    (2, 2, 'Which bike should I get for the city?'),
    (2, 3, 'Get a hybrid.');

INSERT INTO tags (name) VALUES ('leisure'), ('sport'), ('shopping');

INSERT INTO topic_tags (topic_id, tag_id) VALUES
    (1, 1),
    (2, 1),
    (2, 2),
    (2, 3);

Check Your Schema: What Will These Queries Print?

Try to predict the result before you run it. Carol (id = 3) has not started any threads, but she has written messages:

DELETE FROM users WHERE id = 3;
SELECT id, topic_id, author_id, body FROM messages;

+----+----------+-----------+-----------------------------------------+
| id | topic_id | author_id | body                                    |
+----+----------+-----------+-----------------------------------------+
|  1 |        1 |         1 | Where is the fish biting in November?   |
|  2 |        1 |      NULL | At the reservoir, early in the morning. |
|  3 |        2 |         2 | Which bike should I get for the city?   |
|  4 |        2 |      NULL | Get a hybrid.                           |
+----+----------+-----------+-----------------------------------------+

The user is gone, but her messages remain with author_id = NULL – ON DELETE SET NULL did its job. Now delete the “Cycling” thread:

DELETE FROM topics WHERE id = 2;
SELECT id, topic_id, author_id, body FROM messages;
SELECT * FROM topic_tags;

+----+----------+-----------+-----------------------------------------+
| id | topic_id | author_id | body                                    |
+----+----------+-----------+-----------------------------------------+
|  1 |        1 |         1 | Where is the fish biting in November?   |
|  2 |        1 |      NULL | At the reservoir, early in the morning. |
+----+----------+-----------+-----------------------------------------+
+----------+--------+
| topic_id | tag_id |
+----------+--------+
|        1 |      1 |
+----------+--------+

Together with the thread, the cascade deleted both of its messages and three tag links, while the tags themselves stayed in tags. But DELETE FROM users WHERE id = 1 will fail: Alice has a thread, so fk_topics_author returns error 1451. Before you continue with the module, run the script again to restore the original data.

Common Mistakes with Keys

Most key errors in MySQL happen when a foreign key is created: the column types do not match, the parent table does not exist yet, or the data already contains links to nowhere. The error message almost always points at the cause – the most frequent cases are below.

A separate case is SET FOREIGN_KEY_CHECKS = 0. People use it when loading a dump so that the table order does not get in the way. But while the check is off, the database lets any reference through, and after you turn it back on it does not re-check them. Bad data stays in the tables.