NOT NULL, UNIQUE, CHECK and DEFAULT

A constraint is a rule about the data that the database enforces itself, on every insert and update, whichever program writes the row. Four rules cover most columns. NOT NULL says that a column must hold a value. UNIQUE says that no two rows may share a value. CHECK states a condition that every row must meet, such as a price above zero. DEFAULT is a fill-in rather than a test: the value a column takes when an insert leaves it out. A row that breaks a rule is refused with an error, and the statement changes nothing. A rule in the schema beats a check in application code because it also holds for the second application, the bulk import and the person fixing data by hand late at night. The SQLite examples run with one click, on the SQLite 3.49.1 engine inside sql.js 1.14.2, and each difference in PostgreSQL 18 or MySQL 8.4 is pointed out as it comes up.

Four rules on one table

-- mart's products table with all four rules, then six inserts: two keep every rule, four break one each.
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  sku         TEXT    NOT NULL UNIQUE,
  name        TEXT    NOT NULL,
  price_paise INTEGER NOT NULL CHECK (price_paise > 0),
  stock       INTEGER NOT NULL DEFAULT 0
                      CONSTRAINT stock_not_negative CHECK (stock >= 0),
  is_active   INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))
);

-- Kept: stock and is_active are left out, so their defaults fill them in.
INSERT INTO products (sku, name, price_paise) VALUES ('RICE-1KG', 'Basmati rice 1 kg', 14999);
INSERT INTO products (sku, name, price_paise, stock) VALUES ('DAL-500G', 'Toor dal 500 g', 8950, 40);

-- Refused: a second RICE-1KG, a missing name, a price of zero, and stock below zero.
INSERT INTO products (sku, name, price_paise) VALUES ('RICE-1KG', 'Basmati rice again', 15999);
INSERT INTO products (sku, name, price_paise) VALUES ('CHAI-250G', NULL, 12000);
INSERT INTO products (sku, name, price_paise) VALUES ('OIL-1L', 'Coconut oil 1 L', 0);
INSERT INTO products (sku, name, price_paise, stock) VALUES ('RAGI-1KG', 'Ragi flour 1 kg', 6800, -2);

SELECT product_id, sku, name, price_paise, stock, is_active FROM products;

┌────────────┬──────────┬───────────────────┬─────────────┬───────┬───────────┐
│ product_id │   sku    │       name        │ price_paise │ stock │ is_active │
├────────────┼──────────┼───────────────────┼─────────────┼───────┼───────────┤
│ 1          │ RICE-1KG │ Basmati rice 1 kg │ 14999       │ 0     │ 1         │
│ 2          │ DAL-500G │ Toor dal 500 g    │ 8950        │ 40    │ 1         │
└────────────┴──────────┴───────────────────┴─────────────┴───────┴───────────┘

Runtime error near line 17: UNIQUE constraint failed: products.sku
Runtime error near line 18: NOT NULL constraint failed: products.name
Runtime error near line 19: CHECK constraint failed: price_paise > 0
Runtime error near line 20: CHECK constraint failed: stock_not_negative

Two products went in. The first named only three columns, so DEFAULT filled stock with 0 and is_active with 1. The other four inserts were refused, and each error names the rule and the column: a second RICE-1KG breaks the UNIQUE rule, a missing name breaks NOT NULL, and a price of zero and a stock of −2 break the two CHECK conditions. The last error names the constraint stock_not_negative instead of repeating its condition, because the table gave it that name.

The four rules one by one

• NOT NULL refuses a row that leaves the column empty. PostgreSQL and MySQL make every primary key column NOT NULL by themselves; SQLite does so only for an INTEGER PRIMARY KEY, so write it out for any other key.
• UNIQUE refuses a second row with the same value, and the engine builds an index to find duplicates quickly. It may cover several columns together, such as UNIQUE (series, invoice_no), where each column may repeat but the pair may not. UNIQUE accepts any number of NULLs, because a NULL is never equal to anything, not even another NULL.
• CHECK tests a condition on the row being written, and the row is refused only when the condition is false. A condition that comes out NULL passes, and a CHECK cannot look at other rows or tables. MySQL enforces CHECK constraints from version 8.0.16; older versions read them and ignored them.
• DEFAULT gives a value or an expression, such as CURRENT_TIMESTAMP, for a column that the insert does not mention. A default is never used for a NULL that the insert writes on purpose.

Rules that involve several columns

A rule written after a column’s type is a column constraint. A rule that names several columns goes after the last column, as a table constraint: UNIQUE (series, invoice_no), or CONSTRAINT sale_not_above_mrp CHECK (sale_paise <= mrp_paise), which compares two columns of the same row and is therefore allowed everywhere. Both forms are checked in the same way; the table form is simply the only place where a rule can mention more than one column. NOT NULL is the exception, because it always concerns one column: SQLite and MySQL accept it only next to the column, and SQLite reports a table-level NOT NULL as a syntax error. PostgreSQL 18 also accepts CONSTRAINT price_required NOT NULL price_paise among the table constraints, which gives the rule a name of its own. Whatever the form, a CHECK still tests one row at a time, while UNIQUE compares the row with all the others.

Name your constraints

A constraint without a name gets one made up by the engine: products_price_paise_check in PostgreSQL, products_chk_1 in MySQL, which numbers its checks in order, and none at all in SQLite, which prints the condition instead. Writing CONSTRAINT stock_not_negative CHECK (stock >= 0) puts your name into every error message and log line, and it is also the name that ALTER TABLE … DROP CONSTRAINT needs when the rule changes later. Name a constraint after the rule rather than the column, so that the message says what went wrong.

Your turn

-- Add constraints to the columns below. Change nothing after the CREATE TABLE statement.
CREATE TABLE products (
  sku       TEXT,
  name      TEXT,
  stock     INTEGER,
  is_active INTEGER
);

-- Every row of the supplier's sheet is tried; rows that break a rule are skipped.
INSERT OR IGNORE INTO products (sku, name, stock) SELECT sku, name, stock FROM candidates ORDER BY n;
SELECT sku, name, stock, is_active FROM products ORDER BY sku;

# Sample tests: each one fills the table candidates, runs your script on it and compares the rows it returns, in
# order.
tests:
  - name: refuses a negative stock and a missing name, and marks the rest active
    ordered: true
    setup: |
      CREATE TABLE candidates (n INTEGER PRIMARY KEY, sku TEXT, name TEXT, stock INTEGER);
      INSERT INTO candidates VALUES
        (1, 'RICE-1KG', 'Basmati rice 1 kg', 12),
        (2, 'DAL-500G', 'Toor dal 500 g', -4),
        (3, 'CHAI-250G', NULL, 30),
        (4, 'RAGI-1KG', 'Ragi flour 1 kg', 7);
    expected:
      columns: [sku, name, stock, is_active]
      rows:
        - ['RAGI-1KG', 'Ragi flour 1 kg', 7, 1]
        - ['RICE-1KG', 'Basmati rice 1 kg', 12, 1]
  - name: keeps the first of two rows with the same sku, and refuses a row without one
    ordered: true
    setup: |
      CREATE TABLE candidates (n INTEGER PRIMARY KEY, sku TEXT, name TEXT, stock INTEGER);
      INSERT INTO candidates VALUES
        (1, 'OIL-1L', 'Coconut oil 1 L', 9),
        (2, 'OIL-1L', 'Coconut oil, duplicate row', 3),
        (3, NULL, 'Jaggery 1 kg', 15),
        (4, 'SALT-1KG', 'Rock salt 1 kg', NULL);
    expected:
      columns: [sku, name, stock, is_active]
      rows:
        - ['OIL-1L', 'Coconut oil 1 L', 9, 1]
  - name: accepts a stock of exactly zero
    ordered: true
    setup: |
      CREATE TABLE candidates (n INTEGER PRIMARY KEY, sku TEXT, name TEXT, stock INTEGER);
      INSERT INTO candidates VALUES
        (1, 'GHEE-500ML', 'Cow ghee 500 ml', 0),
        (2, 'POHA-500G', 'Thick poha 500 g', -1);
    expected:
      columns: [sku, name, stock, is_active]
      rows:
        - ['GHEE-500ML', 'Cow ghee 500 ml', 0, 1]

UNIQUE in depth: NULLs, letter case and deleted rows Pro: opens with a Pro, Premium or Ultimate pass

CHECK and DEFAULT in depth: NULLs, limits and rules for tables with data Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• NOT NULL, UNIQUE and CHECK are tested on every row that is written; DEFAULT fills only the columns an insert leaves out.
• A broken rule fails the whole statement, which then changes nothing.
• UNIQUE accepts any number of NULLs, and a CHECK passes when its condition is NULL.
• Name constraints after their rule, so that every error message explains itself.
• MySQL enforces CHECK constraints only from 8.0.16; SQLite and PostgreSQL always have.