Struggling to run a query against sqlite db-CodePudding

I am trying to run a migration script against a sqlite database. Below is the error I am getting when attempting the migration queries:

node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

[Error: SQLITE_ERROR: near "categoryId": syntax error] {
  errno: 1,
  code: 'SQLITE_ERROR'
}

sql query I am running to create tables

CREATE TABLE IF NOT EXISTS "Category" (
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR NOT NULL ); 

CREATE TABLE IF NOT EXISTS "Location"(
        id INTEGER PRIMARY KEY NOT NULL,
        name VARCHAR NOT NULL ); 

CREATE TABLE IF NOT EXISTS "Job" (
        id INTEGER PRIMARY KEY NOT NULL,
        createdAt datetime NOT NULL default current_time,
        title VARCHAR NOT NULL,
        description VARCHAR NOT NULL,
        locationId INTEGER NOT NULL,
        FOREIGN KEY(locationId) REFERENCES Location(id),
        categoryId INTEGER NOT NULL,
        FOREIGN KEY(categoryId) REFERENCES Category(id) );

CodePudding user response：

You need to define all columns before defining the constraints, so swap the 2nd and 3rd last lines in your script:

CREATE TABLE IF NOT EXISTS "Job" (
        id INTEGER PRIMARY KEY NOT NULL,
        createdAt datetime NOT NULL default current_time,
        title VARCHAR NOT NULL,
        description VARCHAR NOT NULL,
        locationId INTEGER NOT NULL,
        categoryId INTEGER NOT NULL,
        FOREIGN KEY(locationId) REFERENCES Location(id),
        FOREIGN KEY(categoryId) REFERENCES Category(id) );