This error occurs when you try to insert or update data in a SQLite table that violates a CHECK constraint. CHECK constraints enforce data validation rules at the database level, ensuring only valid data enters your table.
A CHECK constraint is a rule you define on a table to validate data. It prevents invalid data from being stored by checking if values meet specific conditions (like age >= 18, price > 0, or grade between 0-100). When SQLite detects a violation, it rejects the operation and raises this error. The constraint name (if specified) will appear in the error message to help identify which rule was violated.
First, identify what the CHECK constraint requires. Query the table schema using .schema <table_name> in sqlite3 CLI or check your CREATE TABLE statement. The constraint definition shows the exact condition that values must satisfy.
-- Example: Check what constraints exist
.schema Products
-- Output shows: CHECK (Price > 0)Ensure the value you're inserting or updating actually satisfies the constraint condition. For numeric constraints, check for negative values, zeros, or out-of-range numbers. For string constraints, verify the format matches expectations.
-- This will FAIL if constraint is Price > 0
INSERT INTO Products (ProductName, Price) VALUES ('Widget', -10.00);
-- This will SUCCEED
INSERT INTO Products (ProductName, Price) VALUES ('Widget', 10.00);Verify that values are the correct type. Empty strings ('') may behave differently than NULL, and string-to-number conversions can produce unexpected values.
-- If constraint expects numeric range, ensure type is correct
INSERT INTO StudentGrades (Name, Grade) VALUES ('Alice', '150'); -- May fail if not cast correctly
INSERT INTO StudentGrades (Name, Grade) VALUES ('Alice', CAST('85' AS INTEGER)); -- Explicit castWhen defining CHECK constraints, give them explicit names. This makes error messages clearer and helps identify which constraint failed.
-- Named constraint (clearer error messages)
CREATE TABLE voter(
voter_id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
CONSTRAINT check_legal_age CHECK(age >= 18)
);
-- Error will show: "CHECK constraint failed: check_legal_age"If the error happens during table creation or when relying on defaults, check that any DEFAULT values satisfy the CHECK constraint.
-- This will FAIL during creation (default violates constraint)
CREATE TABLE Products (
ProductId INTEGER PRIMARY KEY,
ProductName TEXT,
Price REAL DEFAULT -1.00 CHECK (Price > 0) -- Constraint fails on default
);
-- Correct approach
CREATE TABLE Products (
ProductId INTEGER PRIMARY KEY,
ProductName TEXT,
Price REAL DEFAULT 0.99 CHECK (Price > 0) -- Valid default
);If you need to import legacy data that violates constraints, you can temporarily disable them. Use caution—this bypasses validation.
-- Check current pragma setting
PRAGMA ignore_check_constraints;
-- Disable constraints (use with extreme caution)
PRAGMA ignore_check_constraints = ON;
-- Import/migrate your data
INSERT INTO Products SELECT * FROM legacy_products;
-- Re-enable constraints
PRAGMA ignore_check_constraints = OFF;
-- Verify all data meets constraints before re-enablingCHECK constraint expressions cannot contain subqueries, so you cannot write conditions like CHECK (age > (SELECT MIN(age) FROM users)). If you need complex validation logic, consider using triggers instead.
When using triggers, be aware that if a trigger modifies data in another table and that data violates a CHECK constraint, the error message will reference the table where the constraint failed, not the original table from your statement.
In recent SQLite versions (3.32.0+), you can inspect constraint information via the pragma_table_info() function, but there is no direct PRAGMA to list all CHECK constraints on a table. You must either parse the CREATE TABLE DDL or examine the sqlite_master system table.
For distributed systems or ORMs, ensure that your application logic validates data before sending it to SQLite, as doing constraint checks at the database level (while safer) may be slower than application-level validation.
SQLITE_BUSY: The database file is locked
How to fix 'SQLITE_BUSY: The database file is locked' in SQLite
better-sqlite3: This statement has already been finalized
How to fix "better-sqlite3: This statement has already been finalized" in SQLite
SQLITE_AUTH: Authorization denied
SQLITE_AUTH: Authorization denied
SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified
How to fix "SQLITE_READONLY_RECOVERY: WAL mode database cannot be modified" in SQLite
SQLITE_ERROR: SQL logic error
How to fix "SQLITE_ERROR: SQL logic error" in SQLite