SQLite | Tables | CHECK Constraint
A CHECK constraint validates values stored in a column against a condition. This article explains how to use CHECK constraints.
What Is a CHECK Constraint?
A CHECK constraint verifies that inserted values satisfy a specified condition. The column-level syntax is as follows.
CREATE TABLE table_name (column_name CHECK (condition), ...);
The condition defines which values the column may store, such as restricting values to zero or greater.
To define a condition involving multiple columns, use a table-level constraint as shown below.
CREATE TABLE table_name (column1, column2, ..., CHECK (condition));
Create the following table. The old column has a CHECK constraint and accepts only integers greater than 18.
create table user (id integer, name text, old integer check (old> 18));
sqlite> create table user (id integer, name text, old integer check (old> 18));
sqlite>
Rows whose old value satisfies the condition are inserted successfully.
insert into user values (1, 'devkuma', 19);
insert into user values (4, 'kimkc', 21);
qlite> insert into user values (1, 'devkuma', 19);
sqlite> insert into user values (4, 'kimkc', 21);
sqlite>
sqlite> select * from user;
id name old
---------- ---------- ----------
1 devkuma 19
4 kimkc 21
sqlite>
Inserting a row whose old value does not satisfy the condition produces Error: CHECK constraint failed: user.
insert into user values (7, 'araikuma', 16);
sqlite> insert into user values (7, 'araikuma', 16);
Error: CHECK constraint failed: user
sqlite>
A CHECK constraint therefore limits the values that a column can store.
Defining Complex Conditions with AND and OR
Use AND or OR to build more complex conditions. For example, the following constraint limits old to values greater than 18 and less than 30.
create table user (id integer, name text,
old integer check (old > 18 and old < 30));
The following example restricts gender to man or woman.
create table user (id integer, name text,
gender text check (gender = 'man' or gender = 'woman'));
For a condition involving multiple columns, use a table-level constraint. This example accepts rows where gender is woman or old is greater than 20.
create table user (name text, old integer, gender text,
check (gender = 'woman' or old> 20));
Create the table from the final example.
sqlite> create table user (name text, old integer, gender text, check (gender = 'woman' or old> 20));
sqlite>
Insert several rows. Rows where the gender is woman or the age is greater than 20 are inserted successfully.
insert into user values ( 'dekuma', 16, 'woman');
insert into user values ( 'kimkc', 31, 'man');
insert into user values ( 'araikuma', 24, 'woman');
sqlite> insert into user values ( 'dekuma', 16, 'woman');
sqlite> insert into user values ( 'kimkc', 31, 'man');
sqlite> insert into user values ( 'araikuma', 24, 'woman');
sqlite>
A row that does not satisfy the CHECK condition produces Error: CHECK constraint failed: user.
insert into user values ('happykuma', 15, 'man');
sqlite> insert into user values ('happykuma', 15, 'man');
Error: CHECK constraint failed: user
sqlite>
You can therefore combine AND and OR to define complex CHECK constraints.