CODEWITHSUNDEEP
mysqlsqlforeign-keys
user1032531
user1032531

Reputation: 26281

Delete both children and parent SQL records

I wish to delete a child of a given id, and also delete the parent if the child's parent no longer has any more children after the child is deleted. CASCADE DELETE works great for deleting the children of a deleted parent, however, this is not my intent. Foreign key constraints are enforced, and I do not wish to temporarily disable. I would rather not use a trigger or stored procedure.

CREATE TABLE IF NOT EXISTS parents (
  id INT NOT NULL,
  data VARCHAR(45) NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS children (
  id INT NOT NULL,
  data VARCHAR(45) NULL,
  parentsId INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_children_parents_idx (parentsId ASC),
  CONSTRAINT fk_children_parents
    FOREIGN KEY (parentsId)
    REFERENCES parents (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Sample Data

parents
id data
1  foo1
2  foo2

children

id parentId data
1         1 bar1
2         2 bar3
3         2 bar3

For instance, with the above data, deleting child #1 should delete parent #1, however, deleting child #2 or (not and) #3 should not delete parent #2.

I can do it in three queries (1. get parentsId, 2. delete child, 3. delete parent if it doesn't have any children), however, feel it could/should be better.

I was thinking something similar to the following, however, it is not correct.

DELETE c, p FROM children c
LEFT OUTER JOIN parents p ON p.id=c.parentsId AND c.id!=123
WHERE c.id=123 AND p.id IS NULL;

How can this be performed in one or a maximum of two queries?

Upvotes: 1

Views: 4349

Answers (1)

user1919238
user1919238

Reputation:

There's nothing wrong with your three step design.

This design is straightforward, simple, and effective. There's no reason to suspect that it will be prohibitively inefficient.

There are ways to do it in fewer steps, but they all involve doing "something complicated" and thus have the downside of being harder to develop and less intuitive.

You may "feel it could/should be better" (and it's great to explore alternatives), but I don't see anything better than your proposed design (absent some specific issue, like unacceptable performance).

Upvotes: 2

Related Questions