Why Sqlite parse error: line 1:94: expecting RPAREN, found 'OR' in CHECK constraint?
(1) By Mario Emmenlauer (emmenlau) on 2020-11-12 14:56:14 [link] [source]
Dear SQLite users and developers,
thanks a lot for this awesome software!
I'm really a beginner with SQL but I managed to work with SQLite quite nicely (or so I think). Now I've met a warning that raises my concern. When I open my database file with sqlitebrowser3, it complains:
Sqlite parse error: line 1:94: expecting RPAREN, found 'OR'(CREATE TABLE images_raw(identifier_s1 VARCHAR UNIQUE PRIMARY KEY CHECK(identifier_s1 IS NULL OR (typeof(identifier_s1)='text')), path_s1 VARCHAR UNIQUE CHECK(path_s1 IS NULL OR (typeof(path_s1)='text')), is_internal_b0 INTEGER CHECK(is_internal_b0 IS NULL OR (typeof(is_internal_b0)='integer' AND is_internal_b0 BETWEEN 0 AND 1))))
I assume something is wrong in my CHECK
statements but I fail to see what. I've googled for a while but all the examples I find are similar to mine (as close as I can see). Maybe there should be more (or less) bracketing of the statements?
I want to achieve with CHECK
s that entries can only be of their corresponding C++ type. In this case, identifier_s1
corresponds to a std::string, path_s1
corresponds to a std::string and is_internal_b0
corresponds to a bool.
Is my syntax (and logic) correct? Or how to do this better?
(2) By Richard Hipp (drh) on 2020-11-12 15:15:26 in reply to 1 [link] [source]
I do not think that error message is coming from SQLite. I suspect you are using a third-party GUI of some kind and that third-party GUI is generating the error message you are seeing.
Your CREATE TABLE statement seems to be fine in SQLite. I think this may be a problem in whatever third-party GUI tool you are using.
(4) By Mario Emmenlauer (emmenlau) on 2020-11-12 15:34:38 in reply to 2 [link] [source]
Dear Richard,
thanks so much for this relieving insight. I somehow assumed that sqlitebrowser would come from the SQLite sources, and its warning would have significant authority. But you are right, there was no error or warning from SQLite itself!
Thanks again for your helpful validation! And all the best, stay safe.
(3) By Larry Brasfield (LarryBrasfield) on 2020-11-12 15:29:28 in reply to 1 [link] [source]
It would be more helpful to your potential helpers here if you could show what inputs you provided to induce this error response.
I realize you may think you have done that already by saying "When I open my database ...". However, it would be exceedingly strange for an application with a generic name like 'sqlitebrowser3' to be attempting creation of a normal (as opposed to system) table upon opening the database. Hence, I surmise that you have done something more than just opening the database.
(5) By Mario Emmenlauer (emmenlau) on 2020-11-12 15:40:54 in reply to 3 [link] [source]
Sorry if I did not provide my information!
The string shown in the error message is indeed exactly the string I used to create the database, except I also have IF NOT EXISTS
after CREATE TABLE
, which for some reason is excluded from the error message.
What I did when I said "When I open my database ..." really is just calling sqlitebrowser /path/to/database.db
from the terminal. This sqlitebrowser
is a standard Ubuntu package, where I use the one from Ubuntu 20.04 x86_64:
#> apt-cache show sqlitebrowser
Package: sqlitebrowser
Architecture: amd64
Version: 3.11.2-1build2
Priority: extra
Section: universe/devel
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Arto Jantunen <viiru@debian.org>
[...]
(6) By Tim Streater (Clothears) on 2020-11-12 17:06:29 in reply to 5 [link] [source]
Use the sqlite3 terminal program that can be found at sqlite.org if it is not already on your machine.
(7) By Mario Emmenlauer (emmenlau) on 2020-11-12 18:30:54 in reply to 6 [link] [source]
Thanks @Tim Streater but I'm generally happy with the graphical sqlitebrowser. The warning message is confusing but there are no other problems using it.
Or did you mean to say that there is some specific functionality of the sqlite3 terminal program that may be helpful for me? Like a "sanity check" for the database that provides extra information that could be used to validate my db?
(8) By Keith Medcalf (kmedcalf) on 2020-11-12 18:38:56 in reply to 7 [link] [source]
If the "official" SQLite3 CLI does not throw a hissy fit but some third-party programme does throw a hissy fit, then the problem is with the third-party programme and not with SQLite3 itself, and recourse should be had to the author of the third-party programme to resolve the hissy fit.
(9.1) By Simon Slavin (slavin) on 2020-11-13 05:04:49 edited from 9.0 in reply to 7 [source]
Keith is telling you that the developers of SQLite did not develop sqlitebrowser. If the command works fine in the SQLite tool then we don't have a problem with it. You should test it to figure out who to complain to. Here: I did it for you:
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE images_raw(identifier_s1 VARCHAR UNIQUE PRIMARY KEY CHECK(identifier_s1 IS NULL OR (typeof(identifier_s1)='text')), path_s1 VARCHAR UNIQUE CHECK(path_s1 IS NULL OR (typeof(path_s1)='text')), is_internal_b0 INTEGER CHECK(is_internal_b0 IS NULL OR (typeof(is_internal_b0)='integer' AND is_internal_b0 BETWEEN 0 AND 1)));
sqlite> .schema
CREATE TABLE images_raw(identifier_s1 VARCHAR UNIQUE PRIMARY KEY CHECK(identifier_s1 IS NULL OR (typeof(identifier_s1)='text')), path_s1 VARCHAR UNIQUE CHECK(path_s1 IS NULL OR (typeof(path_s1)='text')), is_internal_b0 INTEGER CHECK(is_internal_b0 IS NULL OR (typeof(is_internal_b0)='integer' AND is_internal_b0 BETWEEN 0 AND 1)));
sqlite>
SQLite has no problem executing that command. The syntax error message is coming from sqlitebrowser, which is not part of SQLite. Complain to the developers of that program.
(10.1) By little-brother on 2020-11-13 05:09:41 edited from 10.0 in reply to 1 [link] [source]
it's certainly SQLite Browser fault (it looks like a problem of table structure parser). I think that you should create issue there.