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?

Which sqlite version do you use? – TTT Feb 24 '12 at 15:55 — TTT, Feb 24 '12 at 15:55

miles82 · Accepted Answer · 2015-04-13T06:43:39.973

69

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;

edited Apr 13 '15 at 06:43

answered Feb 24 '12 at 15:53

miles82

6,584
38
28

7

You save me man, i had to enable it: `PRAGMA foreign_keys = ON;` – Luca Feb 24 '12 at 16:46
2

For those who are looking for how to enable foreign key support you need to change your ConnectionString to "data source=C:\Dbs\myDb.db;foreign keys=true;" (replace C:\Dbs\myDb.db with your sqlite database). For original post: https://stackoverflow.com/questions/4254371/enabling-foreign-key-constraints-in-sqlite/6419268# – Ali Ihsan Elmas Jan 08 '21 at 06:47

Foreign Key constraint doesn't work

1 Answers1

Linked

Related