sqlite3 "foreign key constraint failed"
Asked Answered
B

4

38

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

Butchery answered 15/3, 2013 at 23:52 Comment(3)
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 1Maurili
Don't delete things, mark them as "dead". #503001Tripod
Good example here : https://mcmap.net/q/411055/-keep-getting-foreign-key-constraint-failed-message-in-my-sqlDrayman
I
36

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.

Increasing answered 16/3, 2013 at 16:35 Comment(1)
Also have a look at my question #76840978Eppie
A
18

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.

Answer answered 16/3, 2013 at 0:3 Comment(3)
For some reason I had an impression that that should happen automatically. Isn't it what CASCADE settings is for?Wil
@Wil you are correct. Kyle's answer contradicts the documentation he refers to.Punt
Also have a look at my question #76840978Eppie
C
2

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;
Cola answered 16/3, 2022 at 11:30 Comment(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.Punt
M
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

Myrtie answered 27/11, 2022 at 23:16 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.