Search code examples
sqlsqlitesql-likeglob

CHECK constraint failed

I'm using sqlite3

I have created a table of a class like this

CREATE TABLE Class (
    ClassID char(5) PRIMARY KEY CHECK (ClassID LIKE 'CT[1-9][A-Z]' OR 'AT[1-9][1-9][A-Z]'),
    ClassQuantity int NOT NULL CHECK (ClassQuantity > 0)
);

And when I insert some values to this table

INSERT INTO Class
VALUES ('CT2D', 50);

It shows me an error message

'CHECK constraint failed'.

I have written the values based on the condition in the check. Can someone help me with this problem.

Solution

  • The LIKE expression in your check constraint is trying to use the extended syntax supported by databases like SQL Server and Sybase. However, I don't think this syntax is supported by SQLite. As a workaround, if your version of SQLite has REGEXP, we can use that instead:

    CREATE TABLE Class (
    ClassID char(5) PRIMARY KEY CHECK (ClassID REGEXP 'CT[1-9][A-Z]|AT[1-9][1-9][A-Z]'),
    ClassQuantity int NOT NULL CHECK (ClassQuantity > 0)
);