deleting a row potentially referenced by many rows in another table
Date: Tue, 29 Sep 2009 10:36:47 -0700 (PDT)
I have a situation that is somewhat the opposite of the case in which you would use ON DELETE CASCADE on a foreign key in a child table to ensure that when a row in the parent table was deleted, the delete would cascade to the matching rows in the Child table (see example below).
CREATE TABLE Parent (
parentId INT NOT NULL,
CREATE TABLE Child (
childId INT NOT NULL,
FOREIGN KEY (parentId) REFERENCES Parent(parentId)
In my case, I want to delete a row in the parent table when the last row in the child table referencing that (parent) row is deleted (N:1 from child to parent). Is there any way to do this automatically? Otherwise, it seems that it would be necessary to check the Child table each time a delete is performed to see if any rows still reference the same parent row and then delete it (the parent row) if none are found.
CM Received on Tue Sep 29 2009 - 12:36:47 CDT