Foreign Key constraint doesn't work

Question

I have two tables, theme and quiz, here is their definition:

    CREATE TABLE "theme" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "nom" VARCHAR NOT NULL );

CREATE TABLE quiz(

     id              INTEGER PRIMARY KEY,
     nom         VARCHAR(256) NOT NULL,
     theme      INTEGER NOT NULL,
     niveau      INTEGER NOT NULL,
     pass          INTEGER DEFAULT 1  NOT NULL,
     jok            INTEGER DEFAULT 1 NOT NULL,
    etat            INTEGER DEFAULT 0 NOT NULL,
    FOREIGN KEY (theme) REFERENCES theme(id)
);

The field id (the primary key) in the table theme is a Foreign Key in the quiz table. When i try to insert a record in the table quiz which contain the value 30 for example as a foreign key, the record is inserted successfully in the quiz table although there is no record in the theme table with the id = 30, i mean, wasn't supposed to interdict this insert since i had a Foreign key constraint?

Answer 1

Are you sure foreign key support is enabled?

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;