Designing Tables: Keys and Constraints

Introduction

A well designed table refuses bad data. If a price can never be negative, the database itself can enforce that, so a bug in an app cannot quietly store nonsense. Rules like this are called constraints. In this lesson you also learn how to decide which tables you need in the first place.

Constraints

Each column can carry rules. Run this to build a small table that uses several:

• PRIMARY KEY: identifies each row. It must be unique and not empty.
• NOT NULL: a value is required.
• UNIQUE: no two rows may share the value, such as an email address.
• CHECK (...): a condition every row must satisfy.
• DEFAULT: the value to use when none is given. Sam got the plan free.

Watch the rules work

The second INSERT below breaks a rule, so this block should fail. Run it and read the error message.

The second row repeats an email, so the whole statement fails with a UNIQUE constraint failed error. Now edit the second row to use a new email but age 9, and you will meet CHECK constraint failed. Leave the email out and you will meet NOT NULL constraint failed.

Foreign keys

A foreign key makes the database check the link between tables. A row in orders may not refer to a customer who does not exist. Foreign keys are written as REFERENCES:

The last insert fails with FOREIGN KEY constraint failed, because there is no author 99. Remove that last line and the rest works.

SQLite is unusual here: it keeps foreign key checks off by default for each connection until you run PRAGMA foreign_keys = ON;. The editor in this course turns them on for you. In your own SQLite projects, remember that line. Other databases such as PostgreSQL always enforce them.

The same protection works in reverse: with foreign keys on, you cannot delete a customer who still has orders. You must delete or reassign the orders first.

That fails, because Asha has orders pointing at her.

Relationships between tables

• One-to-many: one customer has many orders. The "many" side holds the foreign key (orders.customer_id).
• Many-to-many: one order contains many products, and one product appears in many orders. You cannot put that in a single column, so you add a junction table (here order_items) with a foreign key to each side.

Splitting repeated data

Imagine a single table that stores every order line with the customer's details copied each time:

If Asha moves, you must change three rows, and if you miss one the data disagrees with itself. The fix is to store each fact once, in its own table, and link with keys. This habit is called normalisation. A simple test: if you are copying the same fact into many rows, it probably belongs in its own table.

Try it yourself

Create a table reviews with: an id primary key, a product_id that references products(id) and cannot be empty, a rating that must be between 1 and 5, and a comment that may be empty. Insert one valid review for product 1, then select everything.

Summary

• Constraints (NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY) make the database reject bad data.
• A foreign key (REFERENCES) keeps links between tables valid. In SQLite, switch them on with PRAGMA foreign_keys = ON;.
• One-to-many uses a foreign key on the "many" side. Many-to-many needs a junction table.
• Store each fact once and link with keys, so a change only has to be made in one place.