CODEWITHSUNDEEP
sqlsqlite
xeroshogun
xeroshogun

Reputation: 1092

sqlite near syntax error

I am new to sql and trying to create a table that has a foreign key reference to another table. I keep getting a near syntax error and can't figure out why. See sql statements below

First table

sqlite> CREATE TABLE nl_central_teams ( 

   ...>  id INTEGER PRIMARY KEY AUTOINCREMENT,

   ...>  team_name VARCHAR(64) NOT NULL,

   ...>  city VARCHAR(64) NOT NULL,

   ...>  main_color VARCAR(64) NOT NULL,

   ...>  created_at DATETIME NOT NULL,

   ...> updated_at DATETIME NOT NULL

   ...> );

first table works fine.

Second table

sqlite> CREATE TABLE managers(

   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,

   ...> first_name VARCHAR(64) NOT NULL,

   ...> last_name VARCHAR(64) NOT NULL,

   ...> team_id INTEGER,

   ...> FOREIGN KEY(team_id) REFERENCES nl_central_teams(id),

   ...> created_at DATETIME NOT NULL,

   ...> updated_at DATETIME NOT NULL

   ...> );

Error: near "created_at": syntax error

I feel like my syntax is correct but can't figure out why I am getting this error. Any help would be appreciated,

Upvotes: 4

Views: 27830

Answers (2)

Dani U
Dani U

Reputation: 9

The problem line is actually the one above your error ref:

FOREIGN KEY(team_id) REFERENCES nl_central_teams(id),

Foreign key support in SQLite is a little tricky. It's probably not working for one of these reasons:

  1. You are on a version earlier than 3.6.9 when foreign key support was introduced.

  2. You are on 3.6.9 or later but your specific install was compiled without foreign key support, which is possible. (See http://www.sqlite.org/foreignkeys.html#fk_enable)

    In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 - foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced.

  3. You have an install that supports foreign key support but it has not been enabled. (See http://www.sqlite.org/faq.html#q22.)

    As of version 3.6.19, SQLite supports foreign key constraints. But enforcement of foreign key constraints is turned off by default (for backwards compatibility). To enable foreign key constraint enforcement, run PRAGMA foreign_keys=ON or compile with -DSQLITE_DEFAULT_FOREIGN_KEYS=1.

Upvotes: 0

Donal
Donal

Reputation: 32713

Foreign key constraints should be at the end of the CREATE TABLE definition.

Your managers CREATE TABLE statement should look like this:

CREATE TABLE managers(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(64) NOT NULL,
    last_name VARCHAR(64) NOT NULL,
    team_id INTEGER,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    FOREIGN KEY(team_id) REFERENCES nl_central_teams(id)
);

See here: http://sqlfiddle.com/#!7/f3f38/1/0

Upvotes: 7

Related Questions