Thanks for the clever ideas. In my case I figured it out by hand (it was a trigger which was inserting a row with a foreign key into another table that no longer existed). But I will make use of those strategies in the future.
On Tue, May 9, 2017 at 11:54 PM, Gwendal Roué <gwendal.r...@gmail.com> wrote: > 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 <m...@google.com> 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 > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
