Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 125 ยท Designing Databases

Constraints

Make the database reject bad data. Learn NOT NULL, UNIQUE, CHECK, DEFAULT and PRIMARY KEY constraints, named constraints and generated columns, with live examples.

A constraint is a rule attached to a table that every row must obey. "Every product has a name." "No two customers share an email." "A price is never negative." Once you declare a rule like that, the database checks it on every INSERT and UPDATE and refuses any change that would break it.

You might wonder why the database should check what your application already validates. The answer is that applications change, have bugs, and aren't the only thing writing data. Import scripts, admin tools, a colleague's quick fix in a SQL console and next year's rewrite all touch the same tables. Constraints are the one place a rule is enforced no matter who writes the data, which makes them your last and most reliable line of defense.

This lesson covers the six kinds of rules you can put on columns: NOT NULL, UNIQUE, PRIMARY KEY, CHECK, DEFAULT and generated columns. Foreign keys are constraints too, and they get their own lesson next.

Column and table constraints

You can write a constraint in two places. A column constraint sits right after the column's type and applies to that column. A table constraint is written as its own item in the column list, usually at the end, and can involve several columns:

SQL
CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,                 -- column constraint
  room TEXT NOT NULL,                     -- column constraint
  guest_email TEXT NOT NULL,
  check_in TEXT NOT NULL,
  check_out TEXT NOT NULL,
  CHECK (check_out > check_in),           -- table constraint
  UNIQUE (room, check_in)                 -- table constraint
);

Anything involving more than one column must be a table constraint. Single-column rules can go either way; column constraints are shorter and keep the rule next to the column it protects.

NOT NULL

NOT NULL says the column must always have a value. It's the most common constraint, and most columns should have it. A missing value should be a deliberate design decision ("customers may leave their phone number empty"), not an accident.

This example fails on purpose:

SQL NOT NULL rejects missing values

Press Run (or Ctrl + Enter) to see the result here.

The first insert works, and description is NULL because it's optional. The second stops the run with Error: NOT NULL constraint failed: products.name.

Remember that NOT NULL only blocks NULL. An empty string '' is a value, so it gets through. If empty text is as bad as missing text, add a CHECK as well (shown below).

UNIQUE

UNIQUE says no two rows may have the same value in the column, or the same combination of values for a multi-column UNIQUE (a, b). Use it for every natural key that isn't your primary key: emails, usernames, product codes, a room on a given date.

SQL Single and multi-column UNIQUE

Press Run (or Ctrl + Enter) to see the result here.

Each column may repeat on its own; only the pair must be unique. The last insert fails with Error: UNIQUE constraint failed: bookings.room, bookings.night.

UNIQUE and NULL

Here's a rule that surprises people: in SQLite, PostgreSQL, MySQL and Oracle, a UNIQUE column can hold any number of NULLs. The reasoning is that NULL means "unknown", and two unknown values aren't known to be equal.

SQL UNIQUE allows many NULLs

Press Run (or Ctrl + Enter) to see the result here.

Both Ben and Chloe have no badge yet, and that's allowed. This is usually exactly what you want for optional identifiers. If it isn't, add NOT NULL. SQL Server is the odd one out: its UNIQUE constraints allow only one NULL (use a filtered unique index to get the standard behavior), and PostgreSQL 15+ lets you choose with UNIQUE NULLS NOT DISTINCT.

PRIMARY KEY

A PRIMARY KEY combines UNIQUE and NOT NULL and marks the column (or columns) as the table's official identifier. A table can have only one. Write it as a column constraint for a single column, or as a table constraint for a composite key:

SQL
CREATE TABLE order_lines (
  order_id INTEGER NOT NULL,
  line_number INTEGER NOT NULL,
  product TEXT NOT NULL,
  PRIMARY KEY (order_id, line_number)
);

Choosing keys is a topic of its own, covered in Primary, Candidate, Composite and Foreign Keys, and SQLite's automatic numbering is explained in Auto-Increment and Identity Columns. Remember SQLite's quirk: add NOT NULL to primary keys that aren't INTEGER PRIMARY KEY.

CHECK

CHECK (condition) accepts a row only if the condition is true. It's the most flexible constraint: any expression that doesn't use subqueries works. Use it for ranges, allowed values, formats and rules that compare columns.

Give important checks a name with CONSTRAINT name. The name appears in the error message, which makes failures much easier to understand. This example fails on purpose:

SQL Named CHECK constraints

Press Run (or Ctrl + Enter) to see the result here.

The last insert stops with Error: CHECK constraint failed: price_not_negative. Try the other rules too: a status of 'sold', a name of ' ', or a sale price higher than the price.

A CHECK passes when the condition is NULL. A CHECK rejects a row only if the condition is false. If it evaluates to NULL ("unknown"), the row is accepted. That's why the office chair, with no sale price, passes sale_price_cents < price_cents: comparing with NULL gives NULL. This is usually what you want for optional columns, but if a value is required, CHECK alone won't enforce it. Add NOT NULL.

A few practical tips:

  • Keep each CHECK focused on one rule, and name it after the rule (price_not_negative), so the error tells you what went wrong.
  • Use CHECK (status IN (...)) as a portable stand-in for enum types. PostgreSQL and MySQL also have ENUM types, but adding a value to them later can be awkward.
  • Checks can't look at other rows or tables. "The total equals the sum of the order lines" needs a trigger or application code.
  • MySQL ignored CHECK constraints entirely before version 8.0.16. On older MySQL servers, the syntax was accepted and silently did nothing.

DEFAULT

DEFAULT gives a column a value to use when an INSERT doesn't mention it. A default isn't really a rule (nothing is rejected), but it's declared with the constraints, so it belongs here. A default can be a constant, a special value like CURRENT_TIMESTAMP, or, in SQLite, any expression inside parentheses:

SQL Defaults fill in missing columns

Press Run (or Ctrl + Enter) to see the result here.

The first post gets every default: status draft, 0 views, today's date and a random 8-character slug. The second overrides two of them. (The query shows the lengths of the date and the slug because their values change every day and every run.)

Two details to know:

  • A default is used only when the column is left out. Inserting an explicit NULL stores NULL, or fails if the column is NOT NULL. It doesn't fall back to the default.
  • CURRENT_TIMESTAMP, CURRENT_DATE and CURRENT_TIME give UTC in SQLite, formatted as 'YYYY-MM-DD HH:MM:SS', 'YYYY-MM-DD' and 'HH:MM:SS'. PostgreSQL's now() and SQL Server's SYSUTCDATETIME() are the usual equivalents there.

Generated columns

A generated column (also called a computed column) has a value calculated from other columns in the same row. You never insert it; the database keeps it correct automatically. SQLite (3.31+), PostgreSQL (12+), MySQL (5.7+) and SQL Server all support them.

SQL Generated columns

Press Run (or Ctrl + Enter) to see the result here.

When the pen's quantity changes to 6, line_total_cents updates to 720 on its own. Try inserting a value into line_total_cents directly: SQLite refuses with cannot INSERT into generated column "line_total_cents".

There are two kinds:

  • VIRTUAL (SQLite's default) computes the value whenever it's read. It takes no space.
  • STORED computes the value when the row is written and saves it on disk. Reads are cheaper and, in some databases, only stored columns can be indexed.

Generated columns are perfect for values you'd otherwise compute in every query, like a normalized search key, a total, or a field pulled out of a JSON document. You can index them, as you'll see in Indexes.

Seeing a table's constraints

In SQLite, the original CREATE TABLE text is stored in the schema table, so the easiest way to see every constraint is to read it back. PRAGMA table_info shows each column's type, NOT NULL flag, default and primary-key position:

SQL Inspecting constraints ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The shop's orders table uses NOT NULL on every column except id (an INTEGER PRIMARY KEY can never be NULL anyway, even though notnull shows 0), a CHECK on status, a default of 0 for shipping and a REFERENCES foreign key on customer_id.

Adding constraints later

Real tables evolve, and sometimes you need a rule after the fact. Most databases let you add a named constraint to an existing table, but the database first checks every existing row and refuses if any violate it:

SQL
-- PostgreSQL, MySQL 8, SQL Server, Oracle
ALTER TABLE products ADD CONSTRAINT price_not_negative CHECK (price_cents >= 0);
ALTER TABLE products ADD CONSTRAINT products_sku_unique UNIQUE (sku);
ALTER TABLE products DROP CONSTRAINT price_not_negative;

-- PostgreSQL: NOT NULL is changed on the column
ALTER TABLE products ALTER COLUMN name SET NOT NULL;

SQLite can't add or drop constraints on an existing table (except by adding a new column with its own constraints). Instead, you rebuild the table, which is shown step by step in ALTER TABLE and DROP TABLE. A UNIQUE rule is the exception: CREATE UNIQUE INDEX adds one at any time.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table has email TEXT UNIQUE (without NOT NULL). How many rows with a NULL email can it hold in SQLite?

    In SQLite, PostgreSQL, MySQL and Oracle, NULLs are not considered equal to each other, so a UNIQUE column accepts any number of them. Add NOT NULL to forbid them.
  2. 2. Given discount INTEGER CHECK (discount BETWEEN 0 AND 50), which insert succeeds?

    NULL BETWEEN 0 AND 50 is NULL, not false, and CHECK only rejects false. Add NOT NULL if the value is required.
  3. 3. A column is status TEXT NOT NULL DEFAULT 'draft'. What does INSERT INTO posts (title, status) VALUES ('Hi', NULL) do?

    A default is only used when the column is left out of the INSERT. An explicit NULL is a value the NOT NULL constraint rejects.
  4. 4. Why give a CHECK constraint a name with CONSTRAINT name?

    A name like price_not_negative makes failures self-explanatory and gives ALTER TABLE ... DROP CONSTRAINT something to refer to.
  5. 5. Which rule can a CHECK constraint NOT enforce?

    CHECK sees only the row being written. Rules that involve other rows or tables need triggers or application code.

Summary

  • Constraints are rules the database enforces on every write, no matter which program does the writing.
  • NOT NULL requires a value; empty text still gets through.
  • UNIQUE forbids duplicate values or combinations, but allows many NULLs (except in SQL Server).
  • PRIMARY KEY is the table's identifier: unique and not null.
  • CHECK accepts rows whose condition isn't false; NULL passes, so pair it with NOT NULL when needed. Name important checks.
  • DEFAULT fills in columns left out of an INSERT; an explicit NULL does not trigger it.
  • Generated columns compute values from other columns, either VIRTUAL or STORED.
  • Read SQLite constraints from sqlite_schema; other databases add them later with ALTER TABLE โ€ฆ ADD CONSTRAINT.

Next: Foreign Keys, the constraint that keeps references between tables valid.