A colleague created a table with a strange UNIQUE key (to get around NULL handling) by adding a COALESCE to enforce NULL values in the unique constraint. I'm receiving the error below when trying to open the database using DbVisualizer Pro, but it seems to open fine when using SQLite Expert Professional (I'm trialing them both):
SQLITE_CORRUPT: The database disk image is malformed (malformed database schema (ux_test) - near "(": syntax error)
DDL:
CREATE TABLE Test (
Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
val1 integer,
val2 integer,
val3 integer
);
CREATE UNIQUE INDEX ux_test ON Test(val1, COALESCE(val2, -1), COALESCE(val3, -1));
I asked DbVisualizer support for help regarding this, and was told that it's a SQLite error. Can anyone offer any insight into this? FYI, the COALESCE() is in the Unique key because of the way SQLite handles NULL values; it doesn't see NULL values being equal in the constraint. Additionally, they prefer to use INSERT OR REPLACE INTO if the constraint is hit, so a BEFORE trigger won't work.
Thanks.
EDIT: CL's answer (below) seems to the issue; DbVisualizer Pro is using SQLite version 3.8.11, vs the current version of 3.18.0. I'm trying to see if I can get an update to the dll's used because it's really a great application. Thank you!
The documentation says:
The ability to index expressions was added to SQLite with version 3.9.0 (2015-10-14). A database that uses an index on expressions will not be usable by earlier versions of SQLite.
Obviously, your DbVisualizer is horribly outdated.