Constraints
Rules that protect your data.
A database is only as good as the data inside it. Duplicate emails, negative ages, orders for customers that don't exist. Bad data causes wrong answers later. Constraints are rules you attach to columns, and the database refuses any row that breaks them.

- Bad data, wrong answers
- Rules on columns
- The database says no
UNIQUE
Helen is starting a members' club. Each email must be used only once, so the email column is unique, and not null. The first insert works. The second uses the same email. Run it. The database refuses, with a unique constraint error. That's a good thing. The bad row never got in.
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
age INTEGER CHECK (age >= 16)
);
INSERT INTO members (email, age) VALUES ('ana@mail.com', 34);
INSERT INTO members (email, age) VALUES ('ana@mail.com', 29);Runtime error near line 7: UNIQUE constraint failed: members.email (19)
CHECK
Check lets you write your own rule. Members must be sixteen or older. Let's try a twelve year old. Run it. Check constraint failed. Again, the database protects you.
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
age INTEGER CHECK (age >= 16)
);
INSERT INTO members (email, age) VALUES ('leo@mail.com', 12);Runtime error near line 6: CHECK constraint failed: age >= 16 (19)
FOREIGN KEY
A foreign key makes sure a row points to something real. In SQLite, you switch this checking on with a pragma. Order items point to orders. So let's try to delete an order that still has items. Run it. The database stops us. No orphaned order lines.
PRAGMA foreign_keys = ON;
DELETE FROM orders
WHERE order_id = 1001;Runtime error near line 3: FOREIGN KEY constraint failed (19)
The constraints you'll meet
Here are the five you'll meet most often. Primary key, unique, and not null for every row. Check for your own rules. And foreign key to link tables safely.
Summary
Constraints stop bad data at the door. Next, how to make several changes safely, all or nothing.
Key takeaways- UNIQUE, NOT NULL
- CHECK (rule)
- FOREIGN KEY