Table constraints accepted without comma
(1) By Konstantin (k.semenenkov) on 2021-12-14 12:28:20 [source]
Hello, According to https://www.sqlite.org/lang_createtable.html "table-constraint" items go after "column-def" items and must be separated with comma, but: create table test ( id int, check (id > 1) check (id < 10)) There is no comma between these check constraints, but this statement does not fail and moreover - second "check" works, insert into test values (11) fails with error "CHECK constraint failed: id < 10". This seems to be a BUG, to my understanding the expected result should be failure of such CREATE TABLE. Tested wtih recent official System.Data.SQLite .net library (sqlite_version() returns 3.37.0) and with SQLiteStudio 3.3.3 (sqlite_version() returns 3.35.4)
(2) By Donald Griggs (dfgriggs) on 2021-12-14 19:45:10 in reply to 1 [link] [source]
Regarding: >...second "check" works
FWIW, both check constraints work for me.
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (
...> id int,
...> check (id > 1)
...> check (id < 10));
sqlite> insert into test values (1);
Error: stepping, CHECK constraint failed: id > 1 (19)
sqlite> insert into test values (11);
Error: stepping, CHECK constraint failed: id < 10 (19)
And, as you might expect, both constraints also work if one does include a comma. I guess if this is a bug, it seems a rather benign one.
(3) By Konstantin (k.semenenkov) on 2021-12-14 20:36:58 in reply to 2 [link] [source]
Right, both constraints work for me as well. I didn't mention that 1st one works because it is defined properly with comma, my point was that 2nd constraint is not ignored. I can not judge the importance of this because I am not aware of other issues severity, but I think this should be either registered as a bug just to know that it exists, OR spec should be updated to mention that comma is optional. For me it is important because I need to parse table definition, but I understand that it can be not important for most of others. And that there can be lot of real tables created in the world without that comma and that also can affect the final decision on this.
(4) By Larry Brasfield (larrybr) on 2021-12-14 20:43:16 in reply to 2 [link] [source]
(also responding to #1)
I guess if this is a bug, it seems a rather benign one.
At one time, it was an unintended yet harmless laxity (except to those sharp-eyed enough to notice and find reason to think or report it as wrong.)
At present, it is intended behavior because, by the time it was discovered, there were an unknown number of SQLite clients who might have become reliant on the laxity. ("Reliant" in the sense of being inconvenienced by a "fix".)
We consider this better left undocumented. Encouraging creation of not-quite-SQL or cluttering the docs and syntax diagrams is too high a price to pay for the negligible benefit to library users.
I hope nobody is disappointed, and please continue filing bug reports.
(5) By Konstantin (k.semenenkov) on 2021-12-14 20:44:44 in reply to 2 [link] [source]
Just to clarify one point regarding first constraint. I know that it could be declared without comma and that would make both constraints valid because in this case it would be a "column-constraint" Issue happens when it is not a part of "column-def" list, but a part of "table-constraint" list.
(6) By Konstantin (k.semenenkov) on 2021-12-14 20:49:46 in reply to 4 [link] [source]
ok, I've got your reasons, thank you!