How to Enable/Disable CHECK Constraints in SQLite

If you ever need to enable or disable all CHECK constraints in SQLite, you can use the ignore_check_constraints PRAGMA statement.

This pragma statement explicitly enables or disables the enforcement of CHECK constraints. The default setting is off, meaning that CHECK constraints are enforced by default.

Syntax

The syntax goes like this:

PRAGMA ignore_check_constraints = boolean;

Where boolean is a boolean value.

To disable CHECK constraints, this can be any of the following:
- false
- no
- off
- 0
To enable CHECK constraints, this can be any of the following:
- true
- yes
- on
- 1

Example

First, let’s create a table with a CHECK constraint:

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

Now we can toggle CHECK constraints on or off as required to either enforce or not enforce that constraint.

Enable CHECK Constraints

Here’s an example of enabling all CHECK constraints:

PRAGMA ignore_check_constraints = 0;

As mentioned, this is the default value anyway. So if you don’t use this PRAGMA statement CHECK constraints will already be set to this value.

Now let’s test that CHECK constraints are in fact enabled by attempting to insert data that violates that CHECK constraint:

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

Result:

Error: CHECK constraint failed: Products

So the CHECK constraint was enforced as expected.

Disable CHECK Constraints

Here’s an example of disabling all CHECK constraints:

PRAGMA ignore_check_constraints = 1;

Now let’s try to insert the same data again and select the result:

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

SELECT * FROM Products;

Result:

ProductId   ProductName  Price     
----------  -----------  ----------
1           Blue Widget  0.0

As expected, the data managed to enter the database, even though it violates the CHECK constraint.