Search code examples
sqliteforeign-keyssqlitemanager

SQLite Foreign keys not working correctly

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

Solution

  • 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.