40

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

Improve this question
3
  • 2
    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 Commented Jun 12, 2017 at 9:35
  • Don't delete things, mark them as "dead". stackoverflow.com/questions/502501/… Commented Jul 9, 2017 at 14:00
  • Good example here : stackoverflow.com/a/47661573/2377343 Commented Jun 10, 2023 at 16:33

5 Answers 5

Reset to default
37

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Also have a look at my question stackoverflow.com/questions/76840478/…
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.

Improve this answer

3 Comments

For some reason I had an impression that that should happen automatically. Isn't it what CASCADE settings is for?
@jayarjo you are correct. Kyle's answer contradicts the documentation he refers to.
Also have a look at my question stackoverflow.com/questions/76840478/…
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;
Improve this answer

1 Comment

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

Improve this answer

Comments

0

Here's a generic way to solve this problem. I was working in C#, so I wrapped these PRAGMA commands in a _context.Database.GetDbConnection().CreateCommand();

Step 1 - allow SQLite to have broken FKs

PRAGMA foreign_keys = false;

Step 2 - save the changes that break your FK In the case if the OP, that was

delete from a where id=1;

Step 3 - find out what the errors are

PRAGMA foreign_key_check;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.