SQLite - CHECK

The SQLite CHECK constraint is used to ensures that all values in a column of a table satisfies specified condition. By defining a CHECK constraint on a table it limits the values that can be entered for this column. Using AND and OR operator, a CHECK constraint can constructed in such a way that can limit the values in certain columns based on values in other columns in the row.

SQLite CHECK constraint with CREATE TABLE

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which CHECK constraint is applied on column Age.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT CHECK (Age >= 21),
  Salary DECIMAL(18,2)
);

Alternatively, it can also be created as mentioned below:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CHECK (Age >= 21)
);

To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say City and Age), the following statement can be used:

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2),
  CONSTRAINT CHK_Employee CHECK(Age >= 21 AND City = 'London')
);

SQLite CHECK constraint with ALTER TABLE

SQLite does not support ALTER TABLE statement to add a CHECK constraint to the existing table. However this can be achieved using following steps:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but CHECK constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce CHECK constraint on Age column of the existing table Employee, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT CHECK (Age >= 21),
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;

DROP CHECK constraint

SQLite does not support ALTER TABLE or DROP statement to drop an existing CHECK constraint from a table. To achieve this, the following steps can be used:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but CHECK constraint dropped
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to drop CHECK constraint from Age column of the existing table Employee, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Employee RENAME TO Employee_old;

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

INSERT INTO Employee SELECT * FROM Employee_old;

COMMIT;

PRAGMA foreign_keys=on;