floflo29 floflo29 - 1 month ago 6
Android Question

SQLite syntax error after adding a second foreign key to my table

I have slightly modified my database and it now raises the following error:

Caused by: android.database.sqlite.SQLiteException: near "location_id": syntax error (code 1): , while compiling: CREATE TABLE present(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price DOUBLE, link TEXT, dob DATE, isAlreadyBought INTEGER, friend_id INT, FOREIGN KEY(friend_id) REFERENCES friend(id), location_id INT, FOREIGN KEY(location_id) REFERENCES location(id) )


I have checked my query many times but I do not manage to see any syntax error, unless adding another foreign key to my
PRESENT_TABLE
should be done differently.

Here are the three queries:

public static final String CREATE_PRESENT_TABLE = "CREATE TABLE "
+ PRESENT_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ NAME_COLUMN + " TEXT, " + PRESENT_PRICE + " DOUBLE, "
+ PRESENT_LINK + " TEXT, "
+ PRESENT_DOB + " DATE, "
+ PRESENT_ISALREADYBOUGHT + " INTEGER, "
+ PRESENT_FRIEND_ID + " INT, "
+ "FOREIGN KEY(" + PRESENT_FRIEND_ID + ") REFERENCES "
+ FRIEND_TABLE + "(id), "
+ PRESENT_LOCATION_ID + " INT, "
+ "FOREIGN KEY(" + PRESENT_LOCATION_ID + ") REFERENCES "
+ LOCATION_TABLE + "(id) " + ")";

public static final String CREATE_FRIEND_TABLE = "CREATE TABLE "
+ FRIEND_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ FRIEND_LOGIN + " TEXT, "
+ FRIEND_PASSWORD + " TEXT, "
+ FRIEND_MAIL + " TEXT " + ")";

public static final String CREATE_LOCATION_TABLE = "CREATE TABLE "
+ LOCATION_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ LOCATION_NAME + " TEXT, "
+ LOCATION_LATITUDE + " DOUBLE, "
+ LOCATION_LONGITUDE + " DOUBLE " + ")";


I create those tables in the following order:

FRIEND --> LOCATION --> PRESENT

EDIT:

Suppose that I remove the second
FOREIGN_KEY
:

public static final String CREATE_PRESENT_TABLE = "CREATE TABLE "
+ PRESENT_TABLE + "(" + ID_COLUMN + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ NAME_COLUMN + " TEXT, " + PRESENT_PRICE + " DOUBLE, "
+ PRESENT_LINK + " TEXT, "
+ PRESENT_DOB + " DATE, "
+ PRESENT_ISALREADYBOUGHT + " INTEGER, "
+ PRESENT_FRIEND_ID + " INT, "
+ "FOREIGN KEY(" + PRESENT_FRIEND_ID + ") REFERENCES "
+ FRIEND_TABLE + "(id) " + ")";


My code does compile.

CL. CL.
Answer

As shown in the documentation, table constraints must come after all the columns:

CREATE TABLE PRESENT(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    [...],
    PRESENT_FRIEND_ID INT,
    PRESENT_LOCATION_ID INT,
    FOREIGN KEY(PRESENT_FRIEND_ID) REFERENCES FRIEND(id),
    FOREIGN KEY(PRESENT_LOCATION_ID) REFERENCES LOCATION_TABLE(id)
);

Alternatively, for single-column constraints, you can use column constraints:

CREATE TABLE PRESENT(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    [...],
    PRESENT_FRIEND_ID INT    REFERENCES FRIEND(id),
    PRESENT_LOCATION_ID INT  REFERENCES LOCATION_TABLE(id)
);