sqlite ignores my foreign key constrain

Question

I created two sqlite tables in android

phone table with primary key "id"

CREATE TABLE BLOCKED_PHONES_TABLE ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 1, KEY_PHONE TEXT UNIQUE,KEY_IS_BLOCKED BIT )

comment table with foreign key "id"

CREATE TABLE COMMENTS_TABLE ( id INTEGER, KEY_COMMENT_TEXT TEXT, FOREIGN KEY(id) REFERENCES BLOCKED_PHONES_TABLE(id))

code:

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_BLOCKED_PHONES_TABLE =
            "CREATE TABLE "+ BLOCKED_PHONES_TABLE +
                    " ( "+ KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL DEFAULT 1, "
                    + KEY_PHONE + " TEXT UNIQUE,"
                    + KEY_IS_BLOCKED+" BIT )";

    db.execSQL(CREATE_BLOCKED_PHONES_TABLE);
}

and

@Override
public void onCreate(SQLiteDatabase db) {
    // SQL statement to create book table

    String CREATE_COMMENTS_TABLE =
            "CREATE TABLE " + COMMENTS_TABLE +
                    " ( "+ KEY_ID+" INTEGER, "
                         + KEY_COMMENT_TEXT+" TEXT, "
                         + "FOREIGN KEY("+KEY_ID+") REFERENCES "+PhoneDal.BLOCKED_PHONES_TABLE+"("+KEY_ID+"))";

    db.execSQL(CREATE_COMMENTS_TABLE);

}

I added some data.

including 2-3 comments to the same phone.

enter image description here

why does the comment table don't refer id as a foreign key?

otherwise it won't have ids that are missing in the phone table.

how can I know my sqlite version?

enter image description here

Edit I added:

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL("PRAGMA foreign_keys=ON;");
    phoneDal.onCreate(sqLiteDatabase);
    commentDal.onCreate(sqLiteDatabase);
}

Max · Answer 1 · 2015-01-01T14:10:28.113

1

Hi first you need to check your SQLite version as all the versions don't support foreign key, and if you are using newer version then you have to turn on that feature as for backward compatibility this feature is turned off by default, please go through the following link for better understanding of how to do it.

http://www.sqlite.org/faq.html#q22
https://www.sqlite.org/foreignkeys.html

and to enable it use

       db.execSQL("PRAGMA foreign_keys=ON;");

hope this helps....:)

edited Jan 01 '15 at 14:10

answered Jan 01 '15 at 14:00

Max

510
5
16

how can I know my sqlite version? I add a photo to my question. – Elad Benda2 Jan 02 '15 at 21:14
1

For sqllite file version, see: http://stackoverflow.com/questions/9646353/how-to-find-sqlite-database-file-version for the engine version, The SQLite sqlite_version function returns the version of the SQLite library. For example: SELECT sqlite_version() AS 'SQLite Version'; – NoChance Jan 02 '15 at 21:19
I have tried your fix. but yet I get many ids in comment table which are not in the phone table. what else can I check? – Elad Benda2 Jan 03 '15 at 00:34
@EmmadKareem i want to check the sqlite in my android project. not just installed on my mach. how can I do it? – Elad Benda2 Jan 03 '15 at 00:37
You can issue the query above from your application. As per your issue, I guess the value you have in "FOREIGN KEY("+KEY_ID+") is empty. Try to debug the string CREATE_COMMENTS_TABLE and capture its value then execute it manually on your command line to ensure it is correctly built. This may help: https://www.sqlite.org/foreignkeys.html – NoChance Jan 03 '15 at 01:10
@EmmadKareem i added the two create table strings – Elad Benda2 Jan 03 '15 at 20:11
I tested your sql and the FK constraint is valid. Maybe you need to issue an explicit Commit at the end of your code so that the definitions take effect. By the way why do you have a "MySQL" tag? – NoChance Jan 03 '15 at 22:45

sqlite ignores my foreign key constrain

1 Answers1