1

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.

Ed Bangga
  • 12,879
  • 4
  • 16
  • 30
Huy Vũ
  • 45
  • 7

3 Answers3

4

You miss to write the OR condition there OR ClassID LIKE 'AT[1-9][1-9][A-Z]'

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

INSERT INTO Class
VALUES ('CT2D', 50);
Ilyes
  • 14,640
  • 4
  • 29
  • 55
  • SQLite does not support this `LIKE` syntax, [see this demo](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=79ee7c342bc75855ba62c12d61f48327). – Tim Biegeleisen Sep 10 '19 at 07:57
  • @TimBiegeleisen The question was tagged with SQL Server tag. You can see the revision. – Ilyes Sep 10 '19 at 07:59
  • The question begins with `I'm using sqlite3` ... I would assume this to be the case, unless the OP doesn't even know its own database. – Tim Biegeleisen Sep 10 '19 at 08:00
  • 1
    @Huy Vũ: As you see, by originally tagging several irrelevant DBMS you have made Sami put in effort in vain. Please refrain from tagging your requests with moot tags in the future. – Thorsten Kettner Sep 10 '19 at 08:07
2

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)
);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 1
    Oh yes, I see! I'm used to SQL Server and I thought it is supported for sqlite3 but i'm wrong! Thanks a lot! – Huy Vũ Sep 10 '19 at 08:02
  • @Huy Vũ: And if `REGEXP` is not available, you can revert to mere string functions (`classid LIKE 'CT%' AND LENGTH(classid) = 4 AND SUBSTR(classid, 3, 1) BETWEEN 'A' AND 'Z' AND ...) OR (classid LIKE 'AT%' AND ...)`). – Thorsten Kettner Sep 10 '19 at 08:02
  • @HuyVũ So you're using SQL Server not SQLite? – Ilyes Sep 10 '19 at 08:05
  • I'm currently learning SQL Server @Sami but my project requires SQLite – Huy Vũ Sep 10 '19 at 12:02
  • @metal [SQLite 3 supports REGEXP](https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query). – Tim Biegeleisen Sep 10 '19 at 12:52
1

use GLOB(). as mentioned here

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

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

sqlite dbfiddle

Ed Bangga
  • 12,879
  • 4
  • 16
  • 30