How to add a foreign key relationship in sqlite

Question

there are two tables

create table A(
    id integer primary key autoincrement,
    subject text not null,
);

create table B(
    id integer primary key autoincrement,
    text integer references A(id)
        on delete restrict
        deferrable initially deferred
        unique
);

B table row should be linked to A. So, I added B.text with a foreign key to A..

I added few random data with pk values 1 and 2 in table A Now, I tried to create B table data with a text value referring to 5 which is not present in A but it still could store..

Why this is working and how to fix it?

Answer 1

You have to enable foreign keys support:

PRAGMA foreign_keys=ON;

Reference: http://www.sqlite.org/foreignkeys.html

Example:

sqlite> create table A(
   ...>     id integer primary key autoincrement,
   ...>     subject text not null
   ...> );
sqlite> 
sqlite> create table B(
   ...>     id integer primary key autoincrement,
   ...>     text integer references A(id)
   ...>         on delete restrict
   ...>         deferrable initially deferred
   ...>         unique
   ...> );
sqlite> insert into a values(1,1),(2,2);
sqlite> insert into b(text) values(3);
sqlite> pragma foreign_keys=on;
sqlite> insert into b(text) values(4);
Error: foreign key constraint failed
sqlite>