SQLite table constraints must be specified after all columns

by

Marking this one for future reference.

TL;DR:  You have to specify all the columns in your table, in a CREATE TABLE statement, before you specify table constraints like primary or foreign keys.

SQLite gives the most useless error messages most of the time.  Case in point:

CREATE TABLE "Foo" (
    "ColumnA" TEXT NOT NULL UNIQUE,
    "ColumnB" TEXT)
CREATE TABLE "Bar" (
    "Column0" TEXT NOT NULL,
    PRIMARY KEY ("Column0"),
    "Column1" INTEGER NOT NULL UNIQUE,
    FOREIGN KEY ("Column1") REFERENCES "Foo" ("ROWID"))

That yields the incredibly helpful error message:

near “”Column1″”: syntax error

Cool story bro.

What it’s trying to say is that it wasn’t expecting any more column definitions after that PRIMARY KEY table constraint.  If you simply declare Column1 before the PRIMARY KEY, it works just fine.

Moar posts

Leave a Comment