SQLite Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL

A constraint is a rule in CREATE TABLE, and SQLite rejects every INSERT or UPDATE that breaks the rule. For example, a constraint can reject an order for a customer who doesn't exist. Without constraints, every program that writes to the file has to catch such rows itself.

The constraints are PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK and FOREIGN KEY. You write each one after a column's type:

CREATE TABLE table_name (
  column type PRIMARY KEY,
  column type NOT NULL,
  column type DEFAULT value,
  column type UNIQUE,
  column type CHECK (condition),
  column type REFERENCES parent_table (parent_column)
);

• PRIMARY KEY identifies each row. No two rows can have the same value.
• NOT NULL rejects NULL, the marker for a missing value.
• DEFAULT fills in a column that an INSERT leaves out.
• UNIQUE rejects a value that another row already has.
• CHECK rejects a row that makes the condition false.
• REFERENCES makes a foreign key: the value has to exist in the parent table, the table that the key points at.

You can also write PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY after the last column, as a table constraint. A key over two columns is written this way: PRIMARY KEY (order_id, product_id).

Create two sample tables, customers and orders, and fill them. Each order points at its customer through customer_id:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  email  TEXT UNIQUE,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'closed'))
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers (id) ON DELETE CASCADE,
  amount      INTEGER
);

INSERT INTO customers (id, name, email) VALUES
  (1, 'Anna', '[email protected]'),
  (2, 'Ben',  NULL);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 300),
  (2, 2, 200);

A PRAGMA is SQLite's command for settings, and the one on the first line turns on foreign keys. SQLite checks foreign keys only on a connection that has run this PRAGMA. The INSERT into customers gives no status, so both customers get 'active' from the DEFAULT:

Run the statements in order, on one database file. Every result and error message below was copied from a run on SQLite 3.50.4.

PRIMARY KEY

A PRIMARY KEY identifies each row: no two rows can have the same key. For example, each customer has an id that no other customer has. A table has at most one primary key, and foreign keys in other tables usually point at that key.

INSERT INTO customers (id, name) VALUES (1, 'Cleo');

UNIQUE constraint failed: customers.id

Anna already has id 1, so SQLite rejects Cleo's row. The message says UNIQUE, because a primary key is unique by definition. If the INSERT leaves out id, SQLite fills in one more than the largest id in the table.

NOT NULL

NOT NULL requires a value in a column. For example, NOT NULL on name rejects a customer without a name.

INSERT INTO customers (name) VALUES (NULL);

NOT NULL constraint failed: customers.name

The message names the table and the column. NOT NULL rejects only NULL. An empty string '' is a value, so SQLite accepts it.

DEFAULT

DEFAULT fills in a column that an INSERT leaves out. For example, a new customer gets the status 'active' when the INSERT gives no status.

INSERT INTO customers (name) VALUES ('Cleo');

SELECT id, name, email, status FROM customers;

Cleo got status active from the DEFAULT, and id 3 from the primary key. Her email has no default, so it is NULL.

UNIQUE

UNIQUE rejects a value that another row already has in the column. For example, UNIQUE on email rejects a second customer with the same email address.

INSERT INTO customers (name, email) VALUES ('Dan', '[email protected]');

UNIQUE constraint failed: customers.email

Anna has that address, so SQLite rejects Dan's row. Ben and Cleo both have no email, and SQLite accepted both rows. UNIQUE never treats two NULLs as equal, so a UNIQUE column can hold any number of NULLs.

CHECK

A CHECK constraint rejects any row that makes its condition false. For example, the CHECK on status rejects a status that is not in its list.

INSERT INTO customers (name, status) VALUES ('Dan', 'paused');

CHECK constraint failed: status IN ('active', 'closed')

'paused' is not in the list, so SQLite rejects the row and repeats the condition in the message. A condition can compare columns of the same row, but can't hold a SELECT (a subquery). To check a value against another table, use a foreign key.

FOREIGN KEY

A FOREIGN KEY accepts only a value that exists in another table. For example, customer_id INTEGER REFERENCES customers (id) rejects an order for a customer who doesn't exist.

INSERT INTO orders (customer_id, amount) VALUES (9, 100);

FOREIGN KEY constraint failed

No customer has id 9, so SQLite rejects the order. The message names no table and no column. This connection ran PRAGMA foreign_keys = ON, so SQLite checks the key.

ON DELETE CASCADE

ON DELETE CASCADE deletes the child rows together with their parent row. For example, the foreign key on orders.customer_id has ON DELETE CASCADE, so deleting a customer (the parent) also deletes the customer's orders (the children).

DELETE FROM customers WHERE id = 2;

SELECT id, customer_id, amount FROM orders;

Ben's order 2 was deleted with him, and Anna's order stays. Without ON DELETE CASCADE, the same DELETE fails with "FOREIGN KEY constraint failed". The default ON DELETE action is NO ACTION, and the others are SET NULL, SET DEFAULT and RESTRICT.

SELECT id, name FROM customers;

What goes wrong

Foreign keys are off on a new connection

SQLite starts every new connection with foreign keys off. On a connection that hasn't run the PRAGMA, SQLite accepts the order for customer 9:

PRAGMA foreign_keys;

INSERT INTO orders (customer_id, amount) VALUES (9, 100);

The PRAGMA returns 0 (off), and the INSERT succeeds without an error. Run PRAGMA foreign_keys = ON on every connection that your application opens, such as each sqlite3.connect() in Python. If the PRAGMA returns no row at all, your SQLite has no foreign key support.

Turning foreign keys on later doesn't check the rows already in the file. PRAGMA foreign_key_check lists the rows that break a foreign key:

PRAGMA foreign_keys = ON;

PRAGMA foreign_key_check;

The rowid column holds the order's id. Order 2 points at a customer who doesn't exist.

A PRIMARY KEY that accepts NULL

A primary key that is not INTEGER accepts NULL. The tags table below has a TEXT primary key:

CREATE TABLE tags (name TEXT PRIMARY KEY);

INSERT INTO tags VALUES (NULL), (NULL);

SELECT count(*) AS tag_rows FROM tags;

Both rows went in with no key. The CREATE TABLE documentation calls a NULL primary key a bug of early versions, kept so that old applications still work. To reject the NULL, write name TEXT PRIMARY KEY NOT NULL, or make the table STRICT or WITHOUT ROWID.

A CHECK that lets NULL through

A condition on NULL gives NULL, not false, so the CHECK accepts the row:

INSERT INTO customers (name, status) VALUES ('Eve', NULL);

SELECT name, status FROM customers WHERE name = 'Eve';

Eve has no status, although the CHECK lists only active and closed. The DEFAULT didn't apply either, because the INSERT gave status an explicit NULL. If every row needs a valid status, add NOT NULL to the column.

Adding a constraint to an existing table

SQLite 3.50.4 can't add a constraint with ALTER TABLE:

ALTER TABLE customers ADD CONSTRAINT name_length CHECK (length(name) > 1);

near "CONSTRAINT": syntax error

SQLite 3.53.0 added two forms to ALTER TABLE: ADD CONSTRAINT for a CHECK, and ALTER COLUMN with SET NOT NULL or DROP NOT NULL. SELECT sqlite_version(); shows which version you run.

On any version, CREATE UNIQUE INDEX adds a UNIQUE rule to an existing table. To add a PRIMARY KEY, or a FOREIGN KEY on an existing column, rebuild the table. Create the new table, copy the rows, drop the old table and rename the new one.

Turn foreign keys off before the rebuild. With foreign keys on, DROP TABLE customers deletes every order through ON DELETE CASCADE. The steps on the ALTER TABLE page also start by turning foreign keys off.

See the constraints in DbSchema

DbSchema connects to a SQLite file, reads its tables and draws them on a diagram. The foreign key from orders to customers shows as a line between the two tables.

Double-click a table header to open the Edit Table dialog. The constraints of the table are on the dialog's tabs: Primary Key / Unique / Indexes, Foreign Keys and Check Constraints. Each column's dialog shows Mandatory ( Not Null ) and Default.

In the SQL Editor, you can run the statements of this article against the same file and watch each constraint reject its row.

Download DbSchema at https://dbschema.com/download.html, connect it to your .db file, and paste the sample statements into the SQL Editor. The free Community Edition covers connecting, the diagram with its foreign key lines, and the SQL Editor.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Sources

1. SQLite documentation: CREATE TABLE
2. SQLite documentation: SQLite Foreign Key Support
3. SQLite documentation: ALTER TABLE
4. SQLite documentation: SQLite Autoincrement
5. SQLite documentation: PRAGMA foreign_key_check