Create a CHECK Constraint in SQLite

In SQLite, you can create a CHECK constraint by adding the applicable code within the CREATE TABLE statement when creating the table.

If a table has a CHECK constraint on it, and you try to insert or update data that violates the CHECK constraint, the operation will fail with an error.

Column-Level CHECK Constraint

Here’s an example of creating a column-level CHECK constraint.

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName, 
    Price 
    CHECK (Price > 0)
);

The part that goes CHECK (Price > 0) is the CHECK constraint.

In this case, it specifies that the price must be greater than zero.

Now let’s see what happens if we try to insert data that violates this constraint.

INSERT INTO Products VALUES 
    (NULL, 'Blue Widget', 0.00);

Result:

Error: CHECK constraint failed: Products

The CHECK constraint worked as expected.

I get the same result if I try to use a negative value.

INSERT INTO Products VALUES 
    (NULL, 'Blue Widget', -1.00);

Result:

Error: CHECK constraint failed: Products

But if I increase it to a value that’s greater than zero, then the INSERT operation succeeds.

INSERT INTO Products VALUES 
    (NULL, 'Blue Widget', 1.00);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Blue Widget  1.0       

Table-Level CHECK Constraint

A table-level CHECK constraint checks data across the whole row, rather than just a single column. In other words, you can use a table-level constraint to check data from multiple columns.

Here’s an example of a table-level CHECK constraint.

CREATE TABLE Products( 
    ProductId INTEGER PRIMARY KEY, 
    ProductName, 
    Price,
    Discount,
    CHECK (Price >= Discount)
);

This table is similar to the first one, except that I’ve added an extra column called Discount.

For the CHECK constraint, I’m now checking that the price is larger than the discount (we don’t want the possibility of having a discount that’s larger than the actual price).

Here’s what happens if I try to insert a discount that’s larger than the price.

INSERT INTO Products VALUES 
    (NULL, 'Blue Widget', 1.00, 2.00);

Result:

Error: CHECK constraint failed: Products

If I adjust the discount so that it’s lower than the price, it’s inserted successfully.

INSERT INTO Products VALUES 
    (NULL, 'Blue Widget', 1.00, 0.50);

SELECT * FROM Products;

Result:

ProductId   ProductName  Price       Discount  
----------  -----------  ----------  ----------
1           Blue Widget  1.0         0.5