syntax error with FOREIGN KEY in CREATE TABLE

Question

I am getting following output on debugger. I am not sure what syntax is missing.

The SQL code is:

CREATE TABLE weeks(Week_Id INTEGER PRIMARY KEY, 
  Day TEXT, 
  Start_Time Text, 
  End_Time Text, 
  Break_Time Text );
CREATE TABLE projects(Project_Id INTEGER PRIMARY KEY,
  Name TEXT, 
  Description Text, 
  Client_Name Text, 
  Location Text );  
CREATE TABLE timesheets(Timesheet_Id INTEGER PRIMARY KEY,
  Project_Id  INTEGER,
  FOREIGN KEY (Project_Id) REFERENCES projects (Project_Id),
  Week_Id INTEGER,
  FOREIGN KEY (Week_Id) REFERENCES weeks (Week_Id));

The error boils down to:

12-09 12:34:20.782: E/SQLiteLog(6490): (1) near "Week_Id": syntax error

It might be possible that your FOREIGN KEY declarations should be after your variable declarations. It's worth a shot... — PearsonArtPhoto, Dec 09 '12 at 13:00

score 24 · Accepted Answer · answered Dec 09 '12 at 13:03

Try moving your FOREIGN KEY lists to after your variables are created.

CREATE TABLE timesheets(Timesheet_Id INTEGER PRIMARY KEY,
  Project_Id  INTEGER,
  Week_Id INTEGER,
  FOREIGN KEY (Project_Id) REFERENCES projects (Project_Id),
  FOREIGN KEY (Week_Id) REFERENCES weeks (Week_Id));

Sebastian · Answer 2 · 2012-12-10T14:47:30.060

5

According to the SQLite syntax (http://www.sqlite.org/lang_createtable.html) you can also write something like this:

CREATE TABLE timesheets(Timesheet_Id INTEGER PRIMARY KEY,
Project_Id  INTEGER REFERENCES projects (Project_Id),
Week_Id INTEGER REFERENCES weeks (Week_Id));

This merges declarations and foreign keys.

edited Dec 10 '12 at 14:47

answered Dec 09 '12 at 14:09

Sebastian

5,177
4
30
47

1

Only issue with this approach is it makes it difficult to migrate these tables to other SQL systems if you end up outgrowing SQLites capabilities. Sticking with the standard `FOREIGN KEY` definitions would be recommended if there are plans to move to another SQL system in the future. – bmcminn Aug 09 '18 at 06:58

syntax error with FOREIGN KEY in CREATE TABLE

2 Answers2