Re: [sqlite] foreign key constraint failure

[Mark Wagner]

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

Re: [sqlite] foreign key constraint failure

[Gwendal Roué]

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

Re: [sqlite] foreign key constraint failure

[nomad]

On Wed May 10, 2017 at 08:34:42AM +0200, Clemens Ladisch wrote:
> Mark Wagner wrote:
> > Is there a way to get sqlite to tell which foreign key constraint is
> > causing a failure?
> 
> No; to make the implementation of deferred constraints easier, it keeps
> track only of the number of remaining foreign key failures, not of their
> origin.

Maybe; If you are developing and can afford to reload/reset invalid
data you can turn off foreign key support and do the DELETE, and then
run the foreign_key_check pragma:

CREATE TABLE a(
id INTEGER PRIMARY KEY
);

CREATE TABLE b(
id INTEGER,
FOREIGN KEY(id) REFERENCES a(id)
);

INSERT INTO a VALUES(1);
INSERT INTO b VALUES(1);
DELETE FROM a;
-- Error: FOREIGN KEY constraint failed

PRAGMA foreign_keys=0;
DELETE FROM a;
-- No Error, but now your data is invalid

PRAGMA foreign_key_check;
--  table   rowid   parent  fkid
--  --  --  --  --
--  b   1   a   0

You could perhaps even run the DELETE inside a transaction and rollback
once you have obtained the information you need, to keep your data
integrity.

Regards,
Mark
-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key constraint failure

[Clemens Ladisch]

Mark Wagner wrote:
> Is there a way to get sqlite to tell which foreign key constraint is
> causing a failure?

No; to make the implementation of deferred constraints easier, it keeps
track only of the number of remaining foreign key failures, not of their
origin.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users