- This is the Table in SQlite:
CREATE TABLE document(
id TEXT NOT NULL,
data TEXT NOT NULL,
CHECK ( json_type(data, '$.documentLines.title') IN ('text') ),
PRIMARY KEY (id)
) STRICT;
- This is the INSERT :
INSERT INTO document (id, data) VALUES ('1',
'{
"documentLines": [
{
"title": "This is a nice text",
"lineNumber": "1",
"content": "bla bla bla"
},
{
"title": "This is a nice text too",
"lineNumber": "2",
"content": "bla bla bla"
},
{
"title": null,
"lineNumber": "3",
"content": "bla bla bla"
}
]
}');
...because the 3. documentLine has "title": null, title is not 'text' and the INSERT should normally throw an [SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: json_type(data, '$.documentLines.title') IN ('text'), but it does not throw (--> the INSERT completes without exception) :(
- The QUESTION is:
How to modify the CHECK-contraint (CHECK ( json_type(data, '$.documentLines.title') IN ('text') )) to make the INSERT throw an Exception?
- I tried:
CHECK ( json_type(data, '$.documentLines.title') IN ('text') )
- I expected:
[SQLITE_CONSTRAINT_CHECK] A CHECK constraint failed (CHECK constraint failed: json_type(data, '$.documentLines.title') IN ('text') <-- But did not throw!