SQLite CHECK constraint

An SQLite CHECK constraint can be assigned to a column definition or created as a table constraint. An SQLite CHECK constraint enforces table integrity by limiting the values of a column.

When a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and returns a NUMERIC value.

If the result of evaluating the expression in the check constraint is 0 or 0.0, then a constraint violation has occurred. If the result is NULL or any other non-zero value, then it is not a violation of the constraint.

Enable check constraints.

PRAGMA ignore_check_constraints=OFF;

Disable check constraints.

PRAGMA ignore_check_constraints=ON;

Syntax

Here is the syntax of SQLite CHECK constraint:

CHECK constraint assigned to a column definition.

CREATE TABLE table_name(
	column_name1 datatype NOT NULL,
	column_name2 datatype NOT NULL CHECK(expression),
	column_name3 datatype NOT NULL,
	...
);

CHECK constraint created as a table constraint.

CREATE TABLE table_name(
	column_name1 datatype NOT NULL,
	column_name2 datatype NOT NULL,
	column_name3 datatype NOT NULL,
	...,
	CHECK(expression)
);

Example

Let’s create a table that will have a SQLite CHECK constraint assigned in the column definition, but also a SQLite CHECK constraint at the table level (the expression will contain several columns with specific conditions).

CREATE TABLE books
(
ID 		INTEGER PRIMARY KEY NOT NULL,
NAME 		VARCHAR(50) NOT NULL CHECK (LENGTH (NAME) <= 50),
PRICE 		INTEGER NOT NULL CHECK (PRICE > 0),
DISCOUNT	INTEGER DEFAULT 0,
DESCRIPTION 	VARCHAR(1000),
CHECK (DISCOUNT >=0 AND PRICE > DISCOUNT) 
);

INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (1, 'SQLite', 10, 'Learn SQLite');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (2, 'SQL', 20, 'SQL for beginners');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (3, 'PL/SQL', 30, 'PL/SQL book');
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (4, 'PHP', 15, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (5, 'Python', 20, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (6, 'HTML', 40, NULL);
INSERT INTO books (ID, NAME, PRICE, DESCRIPTION) 
VALUES (7, 'CSS', 10, NULL);

SELECT * FROM books;

Table of books

ID	NAME	PRICE	DISCOUNT	DESCRIPTION
1	SQLite	10	0	Learn SQLite
2	SQL	20	0	SQL for beginners
3	PL/SQL	30	0	PL/SQL book
4	PHP	15	0	NULL
5	Python	20	0	NULL
6	HTML	40	0	NULL
7	CSS	10	0	NULL

The first attempt to trigger the CHECK constraint, we will try to make an insert with a value on the DISCOUNT column greater than the value on the PRICE column.

INSERT INTO books (ID, NAME, PRICE, DISCOUNT, DESCRIPTION) 
VALUES (8, 'JSP', 10, 15, 'JSP tests');

The SQLite database will return the following error:

CHECK constraint failed: DISCOUNT >=0 AND PRICE > DISCOUNT

The second attempt to trigger the CHECK constraint, we will try to update the NAME column with a value length greater than 50 characters.

UPDATE books 
SET NAME='aaaaaaaaa0123456789bbbbbbbbbb0123456789ccccccccccABC' 
WHERE ID=7;

The SQLite database will return the following error:

CHECK constraint failed: LENGTH (NAME) <= 50