CODEWITHSUNDEEP
androidsqlite
Angel
Angel

Reputation: 1

How to create a foreign key in SQLite

Is it okay to write as follows to define a foreign key when creating tables in SQLite?

// income table create statement

private static final String CREATE_TABLE_income = "CREATE TABLE " + TABLE_income
        + "(" + KEY_IID + " INTEGER PRIMARY KEY," + KEY_AMOUNT + " TEXT," +KEY_ID + " INTEGER FOREIGN KEY REFERENCES TABLE_user(uid)" 
        + KEY_INCOMEDATE + " DATETIME" +KEY_INCOMETYPE + " TEXT" + ")";

Upvotes: 0

Views: 65

Answers (2)

laalto
laalto

Reputation: 152787

You are close but there are still some syntax problems.

  • You need commas between column specifications. Missing in two places before KEY_INCOMEDATE and KEY_INCOMETYPE.

  • The syntax for a foreign key in in a column specification would be just REFERENCES, like KEY_ID + " INTEGER REFERENCES TABLE_User(uid)".

  • Alternatively you can define the foreign key in a table constraint using the FOREIGN KEY (column_name) REFERENCES other_table(other_column) syntax. Table constraints are placed after the column specifications.

Example:

private static final String CREATE_TABLE_income = "CREATE TABLE " + TABLE_income
    + "(" + KEY_IID + " INTEGER PRIMARY KEY," + KEY_AMOUNT + " TEXT," +KEY_ID + " INTEGER REFERENCES TABLE_user(uid)," 
    + KEY_INCOMEDATE + " DATETIME," +KEY_INCOMETYPE + " TEXT" + ")";

For the alternative approach, see Szymon's answer.

To make foreign key constraints enforced, remember to set e.g. PRAGMA foreign_keys = 'on'.

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You should put your foreign key definition at the end, after the columns list:

private static final String CREATE_TABLE_income = "CREATE TABLE " + TABLE_income
        + "(" + KEY_IID + " INTEGER PRIMARY KEY," + KEY_AMOUNT + " TEXT," +KEY_ID + " INTEGER," 
        + KEY_INCOMEDATE + " DATETIME," + KEY_INCOMETYPE + " TEXT, "
        +"FOREIGN KEY (" + KEY_ID + ") REFERENCES TABLE_user(uid))";

Upvotes: 1

Related Questions