Lesson 5 of 15 · 22 min
CHECK, UNIQUE, NOT NULL and DEFAULT
By the end of this lesson you can add NOT NULL, DEFAULT, UNIQUE (single and multi-column) and CHECK constraints (column and table level), name them, and predict which rows they reject.
You've met two constraints already: PRIMARY KEY and REFERENCES. Four more turn business rules into guarantees.
- NOT NULL: a value is required.
- DEFAULT expression: the value used when an INSERT doesn't mention the column. It's not strictly a constraint (it never rejects anything) but it belongs with them.
DEFAULT true,DEFAULT 0,DEFAULT current_date,DEFAULT now(). - UNIQUE: no two rows may have the same value (or the same combination, for
UNIQUE (a, b)). - CHECK (condition): every row must make the condition true (or NULL; see below).
A CHECK can be written on a column (percent_off integer CHECK (percent_off BETWEEN 1 AND 90)) or as a separate line at table level, which is required when the rule compares two columns: CHECK (valid_to >= valid_from). PostgreSQL treats both the same way.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons