Quantcast

foreign key constraint failure

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

foreign key constraint failure

Mark Wagner
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: foreign key constraint failure

Gwendal Roué-2
In reply to this post by Mark Wagner
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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é <[hidden email]>
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 <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...