sqlite near syntax error

Question

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,

Answer 1

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