SQLite Foreign keys not working correctly

Question

The following is a query for creating a table:

CREATE TABLE "FacilityRating"(
    "FacilityRatingId" INTEGER PRIMARY KEY NOT NULL,
    "Stars" VARCHAR,
    "Facility_FacilityId" INTEGER,
    "User_UserId" INTEGER,
    FOREIGN KEY (Facility_FacilityId)
        REFERENCES Facility(FacilityId)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (User_UserId)
        REFERENCES User(UserId)
        ON DELETE CASCADE
        ON UPDATE CASCADE
)

However, when I insert a new row in Facility_FacilityId and User_UserId with some random numbers, SQLite does not give error but adds it anyway.

Here is a snapshot:

enter image description here

Any hint what is going on here? I am using SQLite Manager, an Add-on for Mozilla Firefox

Have you enabled foreign keys with `PRAGMA foreign_keys=on;`? — laalto, May 20 '14 at 18:20
no i have not how does that work?? how can i turn on foreign keys ?? — Bugs Happen, May 20 '14 at 18:50

score 9 · Accepted Answer · answered May 20 '14 at 19:12

The documentation says:

Foreign key constraints must be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

score 2 · Answer 2 · answered Mar 22 '17 at 13:28

2

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.setForeignKeyConstraintsEnabled(true);
}

answered Mar 22 '17 at 13:28

He Termis

31
2

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. – manniL Mar 23 '17 at 22:17

SQLite Foreign keys not working correctly

2 Answers2