CODEWITHSUNDEEP
androidsqlsqlitecommand-line
Lukas Kerbs
Lukas Kerbs

Reputation: 43

Can't find the syntax error in this SQLite statement

I am writing an Android app and need a database for it. I will have three tables but only managed to make one right now. I do them in the console to debug and to implement them in my Javacode later. The following statements were succesfull:

sqlite3 progressapp.db

CREATE TABLE Z_Type(_Z_T_ID INTEGER PRIMARY KEY NOT NULL, 
Description TEXT NOT NULL, Unit TEXT NOT NULL);

But now I want to refference the PK of T_Type in my other table:

CREATE TABLE goals (_Z_id INTEGER PRIMARY KEY NOT NULL, Title TEXT NOT NULL,
Type INTEGER NOT NULL, FOREIGN KEY(Type) REFERENCES Z_Type(_Z_T_ID),
Timeframe TEXT, Goaldate INTEGER);

Is Type INTEGER NOT NULL, FOREIGN KEY(Type) REFERENCES Z_Type(_Z_T_ID) a valid SQLite Statement in Android? It says "Error: near "Timeframe": syntax error" But I simply can't find it due to lack with SQL Experience I guess.

Is there a better way to reference the FK maybe?

Upvotes: 2

Views: 408

Answers (3)

Raul Cuth
Raul Cuth

Reputation: 2469

Try this:

CREATE TABLE goals (_Z_id INTEGER PRIMARY KEY NOT NULL, Title TEXT NOT NULL, Type INTEGER NOT NULL,Timeframe TEXT, Goaldate INTEGER, FOREIGN KEY(Type) REFERENCES Z_Type(_Z_T_ID));

I think that the order is important. For further documentation you could visit sqlite.org/foreignkeys.html

Upvotes: 3

laalto
laalto

Reputation: 152787

In a sqlite CREATE TABLE statement, column definitions come first and table constraints only after that.

FOREIGN KEY(Type) REFERENCES Z_Type(_Z_T_ID) is a table constraint that should go at the end.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

You can define the reference as part of the column definition

CREATE TABLE goals (_Z_id INTEGER PRIMARY KEY NOT NULL, Title TEXT NOT NULL,
Type INTEGER NOT NULL REFERENCES Z_Type(_Z_T_ID),
Timeframe TEXT, Goaldate INTEGER);

Upvotes: 0

Related Questions