SQL from Zero › Module 2: DDL: building the structure
Constraints: NOT NULL, UNIQUE, DEFAULT, CHECK
Add rules to columns so bad data can't get in.
Lesson 10 of 33 · about 14 minutes
Video: House rules for your dataThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
A good shop has rules: every parcel needs an address, and no two lockers share a number. Tables have rules too, called constraints. You add them after a column's type, and the database enforces them every time data is added or changed.
| Constraint | Rule | Example |
|---|---|---|
NOT NULL | This column must always have a value | name TEXT NOT NULL |
UNIQUE | No two rows may have the same value | email TEXT UNIQUE |
DEFAULT | Use this value if none is given | country TEXT DEFAULT 'Unknown' |
CHECK | The value must pass a test | price REAL CHECK (price >= 0) |
You can combine them:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
country TEXT DEFAULT 'Unknown'
);
Why bother, when you could just be careful? Because databases are used by many people and many apps at once. Rules in the database protect the data even when someone else makes a mistake. It's the C in ACID: consistent.
Key ideas
- Constraints are rules the database enforces on every change.
- NOT NULL requires a value, UNIQUE forbids duplicates, DEFAULT fills in a value, CHECK tests a condition.
- Rules in the database protect data from everyone's mistakes, not just yours.
Practise
Loading practice…