how to know where precisely the check constraint failed after insert in SQLite?-CodePudding

I found lots of tutorials explaning check constraints and for the sake of simplicity they all use very few and simple columns.

However my DB is more complex and the columns having a check constraint are more numerous so when I get a CHECK constraint failed: <table name> I don't know where to look for and I spend a lot of time chasing after the error.

Is there any way to get more informations for debug ?

CodePudding user response：

Probably, the simplest (and safe) approach is to run this on a throwaway copy of your database.

PRAGMA ignore_check_constraints=on;

-- Execute your insert

PRAGMA integrity_check;

ignore_check_constraints integrity_check

CodePudding user response：

Use descriptive named check constraints in the table's definition that point right to the table/column(s) that may violate the constraint, like:

CREATE TABLE tablename (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER,
  phone_number TEXT,
  CONSTRAINT constr_tblname_age CHECK(age >= 18),
  CONSTRAINT constr_tblname_phone CHECK(LENGTH(phone_number) = 10)  
);

This statement:

INSERT INTO tablename (name, age, phone_number) VALUES ('Alice', 15, '1234567890');

fails with the error message:

Error: SQLITE_CONSTRAINT: CHECK constraint failed: constr_tblname_age

This statement:

INSERT INTO tablename (name, age, phone_number) VALUES ('Bob', 25, '12345');

fails with the error message:

Error: SQLITE_CONSTRAINT: CHECK constraint failed: constr_tblname_phone

In both cases you will be able to find the source of the error.

See the demo.