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

Answer 1

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.