Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 33 ยท Types and Tables

Constraints and STRICT Tables

Make SQLite enforce your rules: NOT NULL, UNIQUE, CHECK, DEFAULT, conflict clauses and STRICT tables that reject values of the wrong type.

You saw in Dynamic Typing and Type Affinity that SQLite will cheerfully put the text 'free' in an INTEGER column. That flexibility is useful in a few cases, but for most applications you want the opposite: a database that says no to bad data. Constraints are the rules that make it do so, and STRICT tables turn column types into real rules.

The golden rule of data integrity is to enforce important rules in the database as well as in your application code. Applications get rewritten, scripts run against the file directly, and bugs happen. A constraint is the last line of defense.

Rules the database enforces

NOT NULL A value is required.
UNIQUE No two rows may repeat it.
CHECK Any condition you can write.
DEFAULT A value when none is given.
1 / 2

NOT NULL and DEFAULT

NOT NULL forbids missing values. DEFAULT supplies a value when an insert leaves the column out. They work well together.

SQL NOT NULL and DEFAULT

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

Ada gets the default plan free and a join date; Grace gets pro. The last statement stops with NOT NULL constraint failed: members.name. The default CURRENT_DATE is evaluated at insert time, so the date itself changes from day to day. That is why the query only checks that it exists.

Defaults can be constants, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, or an expression in parentheses such as DEFAULT (lower(hex(randomblob(4)))). Note that explicitly inserting NULL into a column does not use the default; it only applies when the column is omitted.

UNIQUE

UNIQUE rejects a second row with the same value. Several NULLs are allowed, because NULL is never equal to NULL.

SQL UNIQUE

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

Both rows with a NULL nick are accepted. The final insert fails with UNIQUE constraint failed: accounts.email. Uniqueness is compared exactly, so '[email protected]' would count as different from '[email protected]'. Add COLLATE NOCASE to the column to make the comparison case-insensitive.

A table-level constraint can cover several columns together:

SQL Composite UNIQUE

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

The same room may appear on different days, and the same day for different rooms, but never the same pair twice. Behind the scenes a UNIQUE constraint creates an index, so lookups on those columns are fast as well.

CHECK

CHECK (condition) rejects any row where the condition is false. (If it evaluates to NULL, the row passes, so combine it with NOT NULL when needed.)

SQL CHECK constraints

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

The two valid rows are inserted. The failing insert gives CHECK constraint failed: price >= 0. A CHECK can use any expression over the row's own columns, such as value ranges, allowed lists, string lengths, and relations between columns (CHECK (end_date >= start_date)). It cannot contain subqueries.

CHECK is the standard way to make a boolean column strict, and to fix the loose typing from last lesson:

SQL A strict boolean and a typed number via CHECK

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

The last insert fails with a CHECK constraint error, because typeof('lots') is text. This works, but it is verbose. That is exactly why STRICT was added.

STRICT tables

Add the word STRICT after the closing parenthesis of CREATE TABLE and SQLite will enforce column types. Every column must then have one of these types: INT, INTEGER, REAL, TEXT, BLOB or ANY.

SQL STRICT rejects the wrong type

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

The text '12' is accepted and converted to the integer 12 (STRICT tables still apply the affinity conversion first, then reject what cannot fit). 'free' is refused with cannot store TEXT value in INTEGER column prices.amount.

What STRICT changes:

  • Values that cannot be converted to the column type are rejected: a real with a fractional part into an INTEGER column fails, as does non-numeric text.
  • Only the six listed type names are allowed. VARCHAR(20), DATETIME and BOOLEAN are errors, so you write TEXT and INTEGER instead.
  • Every column needs a type. The ANY type keeps the old flexible behavior for a column that really needs to hold mixed values.
  • A strict table can still hold NULL unless the column is NOT NULL. Strictness is about types, not missing values.
SQL Table-level options and ANY

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

The ANY column keeps each value's own storage class: real, text and integer. The last statement is refused with an "unknown datatype" error. STRICT can be combined with WITHOUT ROWID using a comma: ) STRICT, WITHOUT ROWID;.

For new projects, STRICT is a good default for ordinary tables. Keep in mind that the table has to be created that way: you cannot add STRICT to an existing table with ALTER TABLE, but you can rebuild it (see Altering Schema Safely). Older tools and SQLite versions before 3.37 cannot open a database that contains a STRICT table.

Conflict clauses: what happens on a violation?

By default a violated constraint aborts the statement with an error. SQLite lets you choose another outcome, per statement, with OR after INSERT or UPDATE:

Clause Behavior on a constraint violation
OR ABORT (default) Stop this statement and undo its changes; the surrounding transaction stays open
OR ROLLBACK Roll back the whole transaction
OR FAIL Stop, but keep changes this statement already made
OR IGNORE Skip just the offending row and continue
OR REPLACE Delete the conflicting existing row, then insert
SQL OR IGNORE and OR REPLACE

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

OR IGNORE skips the duplicate 'sql' row but still adds 'lite', leaving sql blue. OR REPLACE removes the old 'sql' row and inserts the new one, so it ends up red. Be careful with REPLACE: it really is a delete plus an insert, so other columns you did not mention are lost, and delete triggers and foreign-key actions may fire. A more precise tool, ON CONFLICT DO UPDATE, comes in UPSERT and RETURNING.

You can also set a column's default reaction in the table definition, as in email TEXT UNIQUE ON CONFLICT IGNORE, but this hides behavior from readers of the insert statements, so most teams prefer being explicit per statement.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A column is declared INTEGER in an ordinary table. What happens when you insert the text 'free'?

    Ordinary tables use affinity, which only converts values that look numeric. STRICT tables or a CHECK constraint are needed to reject it.
  2. 2. Can a UNIQUE column hold several NULL values?

    SQLite follows the SQL rule that NULLs are distinct for uniqueness, so many rows can have NULL.
  3. 3. Which type name is NOT allowed in a STRICT table?

    STRICT tables allow INT, INTEGER, REAL, TEXT, BLOB and ANY only.
  4. 4. What does INSERT OR IGNORE do when a row violates a UNIQUE constraint?

    OR IGNORE silently skips the conflicting row and processes the others.
  5. 5. Why use INSERT OR REPLACE with care?

    REPLACE is a delete followed by an insert, not an in-place update.

Summary

  • NOT NULL, UNIQUE, CHECK and DEFAULT let the database enforce rules; use them for anything your data must always satisfy.
  • UNIQUE allows several NULLs and creates an index; table-level UNIQUE (a, b) covers combinations.
  • CHECK can express ranges, lists and cross-column rules, and can even police types with typeof().
  • STRICT tables accept only INT, INTEGER, REAL, TEXT, BLOB and ANY, and reject values that cannot be stored as the declared type.
  • OR IGNORE, OR REPLACE and friends change what happens on a violation; REPLACE deletes and reinserts.

Next: Dates, Times and Booleans covers the types SQLite does not have, and how to represent them.