There is a way, but it requires some effort:
First let's define a schema that reproduces your error:
CREATE TABLE t1 (
id INTEGER PRIMARY KEY);
CREATE TABLE t2 (
id INTEGER PRIMARY KEY,
id1 INTEGER REFERENCES t1(id) ON DELETE RESTRICT);
INSERT INTO t1 (id) VALUES (123);
INSERT INTO t2 (id, id1) VALUES (456, 123);
-- error: FOREIGN KEY constraint failed
DELETE FROM t1
OK, error is reproduced.
Now you want to know which foreign key has failed:
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
DELETE FROM t1 -- no error this time
PRAGMA foreign_key_check
-- table:"t2" rowid:456 parent:"t1" fkid:0
This means that row 456 of table t2 has a broken foreign to table t1.
If you want to know which row in t1 can not be deleted:
PRAGMA foreign_key_list(t2)
-- id:0 seq:0 table:"t1" from:"id1" to:"id" on_update:"NO ACTION"
on_delete:"RESTRICT" match:"NONE"
OK so id1 in table t2 gives the id of the t1 row which can not be deleted:
SELECT id1 FROM t2 WHERE id = 456
-- id1:123
This is row 123 of t1 which can not be deleted.
Make sure to rollback the failed transaction, and restore foreign key checks:
ROLLBACK
PRAGMA foreign_keys = ON
Gwendal Roué
> Le 10 mai 2017 à 06:57, Mark Wagner <[email protected]> a écrit :
>
> Is there a way to get sqlite to tell which foreign key constraint is
> causing a failure? Some kind of verbose mode?
>
> Thanks!
>
> sqlite> delete from t;
>
> Error: FOREIGN KEY constraint failed
>
> sqlite>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
