SQlite - Android - Foreign key syntax

Question

I've been trying to get foreign keys working within my Android SQLite database. I have tried the following syntax but it gives me a force close:

private static final String TASK_TABLE_CREATE = "create table "
            + TASK_TABLE + " (" + TASK_ID
            + " integer primary key autoincrement, " + TASK_TITLE
            + " text not null, " + TASK_NOTES + " text not null, "
    + TASK_DATE_TIME + " text not null, FOREIGN KEY ("+TASK_CAT+") REFERENCES "+CAT_TABLE+" ("+CAT_ID+"));";

Any ideas what I might be doing wrong? if you need to see the other table structure then I can, its just a very simple structure for the second with an ID and a name.

Edit:

Here is the error:

03-13 13:42:35.389: ERROR/AndroidRuntime(312): Caused by: android.database.sqlite.SQLiteException: unknown column "taskCat" in foreign key definition: create table reminders (_id integer primary key autoincrement, task_title text not null, notes text not null, reminder_date_time text not null, FOREIGN KEY (taskCat) REFERENCES category (_id));

score 116 · Accepted Answer · edited Sep 22 '15 at 12:49

116

You have to define your TASK_CAT column first and then set foreign key on it.

private static final String TASK_TABLE_CREATE = "create table "
        + TASK_TABLE + " (" 
        + TASK_ID + " integer primary key autoincrement, " 
        + TASK_TITLE + " text not null, " 
        + TASK_NOTES + " text not null, "
        + TASK_DATE_TIME + " text not null,"
        + TASK_CAT + " integer,"
        + " FOREIGN KEY ("+TASK_CAT+") REFERENCES "+CAT_TABLE+"("+CAT_ID+"));";

More information you can find on sqlite foreign keys doc.

edited Sep 22 '15 at 12:49

Dhruva Shastri

25
1
7

answered Mar 13 '11 at 14:25

jethro

18,177
7
46
42

6

This solution didn't work for me. The solution I found was to define it all in one line but with a slightly different syntax. I did this instead of the last 2 lines: TASK_CAT+" INTEGER REFERENCES "+CAT_TABLE+");"; – HotN Sep 01 '11 at 15:59
@jetho I want to add functionallity that once the parent key deleted all child's column record should be deleted too. – Asif Mushtaq Feb 13 '17 at 06:33

score 13 · Answer 2 · answered Mar 15 '13 at 00:13

Since I cannot comment, adding this note in addition to @jethro answer.

I found out that you also need to do the FOREIGN KEY line as the last part of create the table statement, otherwise you will get a syntax error when installing your app. What I mean is, you cannot do something like this:

private static final String TASK_TABLE_CREATE = "create table "
    + TASK_TABLE + " (" + TASK_ID
    + " integer primary key autoincrement, " + TASK_TITLE
    + " text not null, " + TASK_NOTES + " text not null, "
+ TASK_CAT + " integer,"
+ " FOREIGN KEY ("+TASK_CAT+") REFERENCES "+CAT_TABLE+" ("+CAT_ID+"), "
+ TASK_DATE_TIME + " text not null);";

Where I put the TASK_DATE_TIME after the foreign key line.

score 2 · Answer 3 · answered Mar 13 '11 at 14:27

2

As you can see in the error description your table contains the columns (_id, tast_title, notes, reminder_date_time) and you are trying to add a foreign key from a column "taskCat" but it does not exist in your table!

answered Mar 13 '11 at 14:27

aweis

5,350
4
30
46

SQlite - Android - Foreign key syntax

3 Answers3

Linked