1

Is there a generic way to determine whether a row in table parent has rows in "child" tables that are referencing it through their foreign keys? I want to be able to tell ahead of time if it can be deleted, i.e. I don't want to try to delete and then catch the exception if it fails.

E.g.:

CREATE TABLE parent (
  id int unsigned primary key
);
CREATE TABLE child_1 (
  id int unsigned primary key, 
  parent_id int unsigned, 
  CONSTRAINT child_fk1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE RESTRICT
);
...
CREATE TABLE child_n (...);

INSERT INTO parent(id) VALUES (1);
INSERT INTO child_1(id, parent_id) VALUES (1, 1);
DELETE FROM parent WHERE id = 1;

In this case we get:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (mydb.child_1, CONSTRAINT child_fk1 FOREIGN KEY (parent_id) REFERENCES parent (id))

To be clear:

  • I don't know what the child tables are called.
  • I don't want to disable foreign key checks and delete the row regardless.
  • I don't want to do a cascading delete.
  • I don't want to "soft-delete" the row by adding a deleted boolean column and set this to "true".
  • I want to find out if it's possible to delete this one row without deleting the children.

I'm thinking I can run a query I against the information_schema to find what the referencing child tables are. I guess that could be a recursive CTE? And then interrogate these tables in turn to find out whether they reference the particular row in the parent table that I want to delete. And potentially that can all be wrapped up into a nice stored procedure. Will that work?

Or is there some other/better way to do this?

  • You could a query against the information_schema to generate a bunch of parent/child table joins. – danblack Sep 26 '18 at 22:41
  • 2
    The solution via information_schema will be too complex. The solution with delete in a transaction attempt, constraint violation check, and unconditional rollback is more-more simple. – Akina Sep 27 '18 at 5:11
  • Hi @Akina - That is a pretty good idea, I would at least upvote that solution if you wrote it up as an answer. Note that I don't want to do a cascading delete, though. If there are rows in tables with foreign keys defined as ON DELETE CASCADE then these would not be detected with the 'transaction, delete, detect violation, rollback' solution. Unless there is a way to detect that more than one row gets deleted? – dbdemon Sep 27 '18 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.