lundi 4 avril 2016

SQLite Check Constraints and SQLite DB Browser

I am using SQLite DB Browser v3.7.0. If I create a table using the following syntax:

CREATE TABLE "A" (
    "AREA" TEXT NOT NULL DEFAULT 'DEFAULT'
        CONSTRAINT "A-AREA-MAX_LENGTH_CHECK"
            CHECK(LENGTH("AREA") <= 25),
    CONSTRAINT "A-AREA-UPPERCASE_NO_WHITESPACE_CHECK"
        CHECK(UPPER("AREA") = "AREA" AND INSTR("AREA", ' ') = 0)
);

When I go to the "Browse Data" tab of SQLite browser, I get no columns showing up (as if the browser is unable to parse the SQL). This is a problem, since when I click "New Record", I get the exception "Error adding record: near ")": syntax error (INSERT INTO '' VALUES ();)".

SQLite DB Browser 3.7.0 Error

However, if I change the constraint to be either one of:

CHECK(UPPER("AREA") = "AREA")
CHECK(INSTR("AREA", ' ') = 0)

Then SQLite DB Browser appears to parse everything correctly. My question is, is the syntax above invalid SQLite, or is this an issue that needs to be reported to the SQLite DB Browser team? Thanks.

EDIT

I also tested in SQLite DB Browser v3.8.0, with similar results:

SQLite DB Browser 3.8.0 Error

Aucun commentaire:

Enregistrer un commentaire