Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to delete only orphan child rows with ON DELETE?

I have a parent table and a child table. A parent row stores the foreign key to the child row. If I set the constraint on the foreign key to ON DELETE CASCADE then that will throw an error when I want to delete a parent row where the foreign key points to a child row that is in use by additional parent rows.

This SQL query lists the child rows and how many times they occur. Parent row is the person and the child row is the country.

SELECT country_id, country, COUNT(person.id) FROM nobel_prizes.person JOIN nobel_prizes.countries c on c.id = person.country_id GROUP BY country_id;

Using the results from this, I could delete orphaned rows easily. But is there a more proper method to do this?

Tags:

2

Answers


  1. 0

    I think you have a couple of concepts reversed.

    It is customary that the table with the foreign key is called the child. The table referenced by the foreign key is called the parent. In your case, the person table is a child of the countries table, not the other way around.

    If you define the foreign key in person with the ON DELETE CASCADE option, then deleting a parent row from countries results in deleting all child rows in person that reference the deleted row to also be deleted.

    An orphan row is one that has no parent. It’s a violation of referential integrity.

    There is no reverse cascading operation supported by declarative referential integrity (aka foreign keys). That is, if you delete the last row in person for a given country, it does not delete that country. It is not a violation of referential integrity for a row in countries to have zero child rows in person depend on it. That’s not an orphan, it’s just a parent with no children (yet).

    If you want to manually find countries that have zero persons referencing it, use a query like this:

    SELECT country_id, country, COUNT(person.id) 
FROM nobel_prizes.person 
LEFT OUTER JOIN nobel_prizes.countries c on c.id = person.country_id 
GROUP BY country_id
HAVING COUNT(person.id) = 0;

    You need to use outer join in this case, because an inner join would match only those rows in countries that have one or more matching rows in person. To find childless countries, you specifically want those with zero child rows in person.

    I would not expect there to be a need to delete rows from countries. There are currently only 195 countries on Earth.

    Login or Signup to reply.
  2. 0

    A solution which uses AFTER DELETE trigger:

    -- tables
CREATE TABLE child (
  id INT PRIMARY KEY 
);
CREATE TABLE parent (
  id INT PRIMARY KEY,
  child_id INT,
  FOREIGN KEY (child_id) REFERENCES child (id)
);

    -- trigger which deletes orphan childs
CREATE TRIGGER tr
AFTER DELETE ON parent
FOR EACH ROW 
DELETE FROM child
WHERE child.id = OLD.child_id -- test a child of deleted parent row
  AND NOT EXISTS (            -- check that there is no another references
    SELECT NULL
    FROM parent
    WHERE OLD.child_id = parent.child_id
    );

    -- sample data
INSERT INTO child VALUES (1), (2), (3);
INSERT INTO parent VALUES (11,1), (12,1), (21,2), (31,3), (32,3);
SELECT * FROM parent;
SELECT * FROM child;
    id child_id
    11 1
    12 1
    21 2
    31 3
    32 3
    id
    1
    2
    3 
    -- delete a row. According child is not deleted because another reference exists.
DELETE FROM parent WHERE id = 11;
SELECT * FROM parent;
SELECT * FROM child;
    id child_id
    12 1
    21 2
    31 3
    32 3
    id
    1
    2
    3 
    -- delete a row. A child become orphan one, and it is deleted too
DELETE FROM parent WHERE id = 21;
SELECT * FROM parent;
SELECT * FROM child;
    id child_id
    12 1
    31 3
    32 3
    id
    1
    3 
    DELETE FROM parent WHERE id = 12;
SELECT * FROM parent;
SELECT * FROM child;
    id child_id
    31 3
    32 3
    id
    3

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search