Can a SQLite CHECK constraint compare values in different columns?
Quick answer
Yes. A SQLite table-level CHECK can compare columns in the same row, for example CHECK (start_value <= end_value). SQLite checks the expression when a row is inserted or updated; a result of zero violates the constraint. This does not enforce rules across multiple rows. Also, a NULL result is not considered a violation, so use NOT NULL if either value must be present. [S1]
The question, the details, the next step.
Can a CHECK constraint compare two different columns in SQLite? I want each row’s start value to be no greater than its end value.
Yes. Put a table-level CHECK in the CREATE TABLE statement and write an expression comparing the two columns, such as CHECK (start_value <= end_value). SQLite evaluates the check when a row is inserted or updated; if the expression evaluates to zero, the change violates the constraint. [S1]
Here is a disposable example. The table name and values are illustrative; use names and data types that fit your own database:
CREATE TABLE demo_range (
start_value INTEGER NOT NULL,
end_value INTEGER NOT NULL,
CHECK (start_value <= end_value)
);The constraint applies to the values in each row. It does not compare one row with another.
What would valid and invalid test inserts look like, and how can I check the result?
Try a row where the start is less than the end, and another where they are equal. Both meet the stated rule. Then try a row where the start is greater; that row violates the check. For example:
INSERT INTO demo_range (start_value, end_value) VALUES (2, 5);
INSERT INTO demo_range (start_value, end_value) VALUES (4, 4);
INSERT INTO demo_range (start_value, end_value) VALUES (8, 3);The first two satisfy start_value <= end_value. The third produces a CHECK constraint violation, so it is not accepted as an ordinary successful insert. To inspect rows that were accepted, run:
SELECT start_value, end_value FROM demo_range;Run these tests on the disposable table rather than using sample inserts in a table containing data you need.
Do I need NOT NULL too? What happens if one of the values is missing?
If both values must be present, keep NOT NULL on both columns, as in the example. This matters because SQLite does not treat a NULL result from a CHECK expression as a violation. [S1] If a column allows NULL, comparing it with another value can produce NULL rather than a definite true or false result, so the check alone does not establish that both values exist.
Choose the rule explicitly: if missing endpoints are allowed, decide how those rows should be represented and whether the comparison should apply only when both are present. If missing values are not allowed, NOT NULL states that requirement directly. The example uses the latter policy.
Could I use this to guarantee that a range does not overlap any other row in the table?
No. This CHECK compares columns in the row being inserted or updated; it is not a way to enforce a condition across multiple rows. SQLite CHECK expressions cannot contain subqueries. [S1] So CHECK (start_value <= end_value) can enforce the within-row ordering rule, but it cannot by itself determine whether that row overlaps another stored range.
For the row-level rule, the reusable pattern is:
CREATE TABLE your_table (
start_value INTEGER NOT NULL,
end_value INTEGER NOT NULL,
CHECK (start_value <= end_value)
);Replace the table name and column definitions as appropriate. The supplied rule does not establish a solution for cross-row overlap enforcement; treat that as a separate database-design question.
A practical checklist
Write the comparison using the two column names, such as
CHECK (start_value <= end_value).Place the table-level CHECK inside the table definition.
Decide whether either value may be NULL; add NOT NULL where presence is required.
Test a less-than case, an equal case, and a greater-than case on a disposable table.
Keep cross-row rules, such as overlap checks, separate from this row-level constraint.
A few more useful answers
Does the CHECK run only when a row is first inserted?
No. SQLite evaluates CHECK expressions when rows are inserted or updated. [S1] The comparison therefore applies to the row values at either of those operations.
Does this constraint prevent two rows from having the same values?
No. The example checks the relationship between two columns within each row; it does not establish uniqueness or compare rows with each other.
Why include NOT NULL if the CHECK already compares the columns?
Because a CHECK expression that evaluates to NULL is not considered a violation. [S1] NOT NULL separately requires a value in each column.
Sources
References used for this guide.
- [S1] SQLite CREATE TABLE: CHECK constraint behavior sqlite.org · Checked