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.
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 (
child_fk1FOREIGN KEY (
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 booleancolumn 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?