[android] SQlite - Android - Foreign key syntax

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));

This question is related to android sqlite foreign-keys

The answer is


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.


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!


Examples related to android

Under what circumstances can I call findViewById with an Options Menu / Action Bar item? How to implement a simple scenario the OO way My eclipse won't open, i download the bundle pack it keeps saying error log getting " (1) no such column: _id10 " error java doesn't run if structure inside of onclick listener Cannot retrieve string(s) from preferences (settings) strange error in my Animation Drawable how to put image in a bundle and pass it to another activity FragmentActivity to Fragment A failure occurred while executing com.android.build.gradle.internal.tasks

Examples related to sqlite

getting " (1) no such column: _id10 " error Laravel: PDOException: could not find driver auto create database in Entity Framework Core How to open .SQLite files Accessing an SQLite Database in Swift When does SQLiteOpenHelper onCreate() / onUpgrade() run? Attempt to write a readonly database - Django w/ SELinux error Android sqlite how to check if a record exists How can I add the sqlite3 module to Python? "Insert if not exists" statement in SQLite

Examples related to foreign-keys

Migration: Cannot add foreign key constraint The ALTER TABLE statement conflicted with the FOREIGN KEY constraint Can a foreign key refer to a primary key in the same table? Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why? MySQL Error 1215: Cannot add foreign key constraint MySQL Cannot Add Foreign Key Constraint Delete rows with foreign key in PostgreSQL How to remove constraints from my MySQL table? MySQL - Cannot add or update a child row: a foreign key constraint fails How to remove foreign key constraint in sql server?