sqlite3 "foreign key constraint failed"

Question

I've set up two tables:

CREATE TABLE A
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT
 );

CREATE TABLE B
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    id2 INTEGER,
    book TEXT,
    FOREIGN KEY(id2) REFERENCES A(id)
);

After I insert data into A, it looks like this:

1    John

2    Amy

3    Peter

After I insert data into B, it looks like this:

1     1     Lord of the Rings

2     1     Catch 22

3     2     Sum of All Fears

4     3     Hunt for Red October

I then execute the following statement:

delete from a where id=1;

I get the following: "Error: foreign key constraint failed"

I then restart sqlite3 and try again but this time I enter this first:

PRAGMA foreign_keys = 1;

it still doesn't work......

Setting `PRAGMA foreign_keys = 1` actually enforces the foreign key constraint. I get the impression that you are trying to disable it, which means that you should be setting it to 0 instead of 1 — goonerify, Jun 12 '17 at 09:35
Don't delete things, mark them as "dead". https://stackoverflow.com/questions/502501/database-to-delete-or-not-to-delete-records — CAD bloke, Jul 09 '17 at 14:00
Good example here : https://stackoverflow.com/a/47661573/2377343 — T.Todua, Jun 10 '23 at 16:33

score 32 · Answer 1 · edited May 10 '16 at 15:01

32

Table B has rows whose foreign key references the primary key value of the Table A row you are trying to delete so deleting it would violate the integrity of your database.

You could include ON DELETE CASCADE in your foreign key definition. With that, when you delete an entry from Table A, any entries in Table B linked to the deleted row would also be deleted. Don't know if that's appropriate for your application.

edited May 10 '16 at 15:01

tijko

7,599
11
44
64

answered Mar 16 '13 at 16:35

phaworth

399
2
8

Also have a look at my question https://stackoverflow.com/questions/76840478/sqliteconstraintexception-foreign-key-constraint-failed-code-787 – Taimoor Khan Aug 05 '23 at 07:34

score 15 · Answer 2 · edited Jul 23 '17 at 10:41

15

The "problem" is that you have set a foreign key on table B.

foreign key(id2) references A(id)

This means that column id2 in table B references column id in table A. Both Lord of the Rings and Catch 22 from Table B are linked to John from Table A. Therefore you cannot delete John without first deleting these other two entries from Table B first.

The alternative would be to remove the foreign key.

See this documentation for more details.

edited Jul 23 '17 at 10:41

Yunnosch

26,130
9
42
54

answered Mar 16 '13 at 00:03

Kyle

17,317
32
140
246

4

For some reason I had an impression that that should happen automatically. Isn't it what CASCADE settings is for? – jayarjo Aug 26 '19 at 06:38
@jayarjo you are correct. Kyle's answer contradicts the documentation he refers to. – nicbou Jul 28 '23 at 12:50
Also have a look at my question https://stackoverflow.com/questions/76840478/sqliteconstraintexception-foreign-key-constraint-failed-code-787 – Taimoor Khan Aug 05 '23 at 07:34

score 1 · Answer 3 · answered Mar 16 '22 at 11:30

1

You can try this:

CREATE TABLE A (
id   INTEGER NOT NULL
             PRIMARY KEY AUTOINCREMENT,
name TEXT);

CREATE TABLE B (
id   INTEGER NOT NULL
             PRIMARY KEY AUTOINCREMENT,
id2  INTEGER REFERENCES A (id),
book TEXT);

for delete :

PRAGMA foreign_keys = 0;
DELETE from A where id = 1;
PRAGMA foreign_keys = 1;

answered Mar 16 '22 at 11:30

Tuncay İNCE

11
1

That does not fix the problem, it just ignores it. The deletion will not cascade, and leave you with a bunch of orphaned rows in table B. – nicbou Jul 28 '23 at 12:48

score 0 · Answer 4 · answered Nov 27 '22 at 23:16

0

Check the data you are inserting in your foreign key. I got the same error, and it was to a bad data I inserted

answered Nov 27 '22 at 23:16

Freedisch

124
1
9

sqlite3 "foreign key constraint failed"

4 Answers4

Linked