Any way to get the specific foreign key constraint that is failing as a result of executing a single-CodePudding

I'm using SQLite as the backend for an iOS app. I am facing a bug where a particular delete operation is not executing due to a failed foreign key constraint. Despite narrowing down the culprit to four different foreign key constraints, I'm still not sure which one it is. Is there a way for SQLite to tell me which specific foreign key constraint is causing the error?

CodePudding user response：

You could always use a query to pre-check and determine the conflicts. Something along the lines of :-

CREATE TABLE IF NOT EXISTS parent (id INTEGER PRIMARY KEY,name TEXT);
INSERT INTO parent (name) VALUES ('parent1'),('parent2'),('parent3'),('parent4');

/* This being the pre-check query */
SELECT 
    2 IN (SELECT id FROM parent) AS FK1CHECK, 
    3 IN (SELECT id FROM parent) AS FK2CHECK, 
    10 IN (SELECT id FROM parent) AS FK3CHECK;

where 2 (exists), 3 (exists) and 10 (would be FK conflict)

obviously you would bind the 3 parameters/values to be checked so the query would be:-

SELECT 
    ? IN (SELECT id FROM parent) AS FK1CHECK, 
    ? IN (SELECT id FROM parent) AS FK2CHECK, 
    ? IN (SELECT id FROM parent) AS FK3CHECK;

for simplicity just the 1 parent table but that would be adjusted to suit the situation

The result :-

1   1   0

i.e. F3CHECK, as it is 0, indicates a FK conflict would result

Additonal

Regarding the comment:-

i see why the third query would fail, but i don't see why it would be a failure of a foreign key constraint. it's just a nonexistent record, and you haven't defined any foreign key constraints here. so there really shouldn't be any foreign key constraints failures anyways. struggling to see how this relates to my issue

There aren't 3 queries it is a single query that outputs 3 columns, via 3 sub queries, each indicating whether the relationship would work. That is it is effectively doing what the FK constraint would do.

As you are struggling to understand then the following demonstrates, step by step :-

/* drop the demo tables if they already exist */
DROP TABLE IF EXISTS child1;
DROP TABLE IF EXISTS child2;
DROP TABLE IF EXISTS child3;
DROP TABLE IF EXISTS parent;
/* turn on Foreign Key Support */
PRAGMA foreign_keys = ON;
/* Show the Foreign Key Support status */
PRAGMA foreign_keys;

/* create the parent table which will be a parent to 3 child tables */
CREATE TABLE IF NOT EXISTS parent (id INTEGER PRIMARY KEY, name TEXT);
/* create the 3 child tables with a Foreign Key constraint */
CREATE TABLE IF NOT EXISTS child1 (id INTEGER PRIMARY KEY, parentid INTEGER REFERENCES parent(id));
CREATE TABLE IF NOT EXISTS child2 (id INTEGER PRIMARY KEY, parentid INTEGER REFERENCES parent(id));
CREATE TABLE IF NOT EXISTS child3 (id INTEGER PRIMARY KEY, parentid INTEGER REFERENCES parent(id));

/* Add some parent rows */
INSERT INTO parent VALUES (1,'P1'),(2,'P2'),(3,'P3');

/* Attempt to insert three rows 
    child1 row with relationship to parent P1 (i.e. parentid = 1), 
    child2 with relationship to P2 (i.e. parentid = 2) and
    child3 with relationship to P3 (i.e. parentid = 3)
    but first PRE CHECK
*/
/* THE PRECHECK */
SELECT (SELECT 1 /*parentid value for child1 insert*/ IN (SELECT id FROM parent)) AS check1,
    (SELECT 2 /* parentid value for child2 insert */ IN (SELECT id FROM parent)) AS check2,
    (SELECT 3 /* parentid value for child3 insert */ IN (SELECT id FROM parent)) AS check3
;
/* This results in 1,1,1 i.e. ok to insert all three*/ :-


/* The the inserts */
INSERT INTO child1 (parentid) VALUES(1 /* parentid value for child1 insert*/);
INSERT INTO child2 (parentid) VALUES(2 /* parentid value for child2 insert*/);
INSERT INTO child3 (parentid) VALUES(3 /* parentid value for child3 insert*/);

/* Show the resultant data */
SELECT * FROM parent 
    LEFT JOIN child1 ON child1.parentid = parent.id
    LEFT JOIN child2 ON child2.parentid = parent.id
    LEFT JOIN child3 ON child3.parentid = parent.id
;

/* Attempt to insert three rows 
    child1 row with relationship to parent P2 (i.e. parentid = 1), 
    child2 with relationship to P3 (i.e. parentid = 2) and
    child3 with relationship to non-existent (i.e. parentid = 3)
    but first PRE CHECK
*/
SELECT (SELECT 2 /*parentid value for child1 insert*/ IN (SELECT id FROM parent)) AS check1,
    (SELECT 3 /* parentid value for child2 insert */ IN (SELECT id FROM parent)) AS check2,
    (SELECT 10 /* parentid value for child3 insert */ IN (SELECT id FROM parent)) AS check3 
;
/* result will be 1,1,0 i.e. the 3rd insert will fail */

INSERT INTO child1 (parentid) VALUES(2 /* parentid value for child1 insert*/);
INSERT INTO child2 (parentid) VALUES(3 /* parentid value for child2 insert*/);
INSERT INTO child3 (parentid) VALUES(10 /* parentid value for child3 insert*/);

When run the first result is from the PRAGMA foreign_keys :-

i.e. foreign key support is true 1, so foreign key support is turned on

The second result is the PRE-CHECK :-

as check1 is 1 then the first insert will not result in a foreign key conflict/failure.
as check2 is 1 then the second insert will not result in a foreign key conflict/failure
as check3 is 1 then the thrid insert will not result in a foreign key conflict/failure

The third result a query after the inserts :-

i.e. the respective child1/2/3 rows have been inserted.

The fourth/last is the PRE-CHECK for the 2nd set of inserts :-

As highlighted the check3 INDICATES that a foreign key conflict/failure WILL happen if the inserts go ahead. The result could be used to determine the ensuing logic and determine which of the foreign keys would cause the conflict.

Lastly as the above was undertaken with an SQLite tool (Navicat) then the INSERTS are attempted the messages being :-

INSERT INTO child1 (parentid) VALUES(2 /* parentid value for child1 insert*/)
> Affected rows: 1
> Time: 0.083s


INSERT INTO child2 (parentid) VALUES(3 /* parentid value for child2 insert*/)
> Affected rows: 1
> Time: 0.074s


INSERT INTO child3 (parentid) VALUES(10 /* parentid value for child3 insert*/)
> FOREIGN KEY constraint failed
> Time: 0s

i.e. as was DETERMINED by the PRE-CHECK (check3 being 0) the attempt to insert into child3 failed with the predicted outcome.

Of course the above would have to be tailored to suit. A more specific answer could have been given if more specific detail were provided.

CodePudding user response：

There is no way that I know of to get directly from SQLite the specific foreign key constraint that raised the error.

What you can do, for debugging purposes, to find that constraint, is to turn off foreign key constraint checks (they are turned off by default) by running:

PRAGMA foreign_keys = OFF;

Then run your code and insert the row(s) that violate the constraints (you won't get any errors).

After that, run:

SELECT * FROM pragma_foreign_key_check();

You will get a resultset consisting of 4 columns: table, rowid, parent and fkid for the row(s) of any table in the database that violate a foreign key constraint.

Check the documentation for pragma_foreign_key_check() and the answer I gave in a similar question.