How to enforce referential integrity on Single Table Inheritance?

Question

I've read some of Bill Karwin's answers about single table inheritance and think this approach would be good for the setup I am considering:

Playlist
--------
id AUTO_INCREMENT
title

TeamPlaylist
------------
id REFERENCES Playlist.id
teamId REFERENCES Team.id

UserPlaylist
------------
id REFERENCES Playlist.id
userId REFERENCES User.id

PlaylistVideo
-------------
id
playlistId REFERENCES Playlist.id
videoId REFERENCES Video.id

All the CASCADE options are set to DELETE which will work correctly for when a Playlist is deleted, however, what happens if a User or Team is deleted?

ie. If a User is deleted, the rows in UserPlaylist will be deleted but the referenced rows in Playlist and PlaylistVideo will remain. I thought about enforcing this as a TRIGGER AFTER DELETE but there is no way of knowing if the delete request came about because the Playlist was deleted or if the User was deleted.

What is the best way to enforce integrity in this situation?

Edit (Provided ERD)

Answer 1

What you can do is implement triggers on your Users and Team tables that execute whenever rows get deleted from either:

User table:

DELIMITER $$
CREATE TRIGGER user_playlist_delete 
BEFORE DELETE ON User FOR EACH ROW
BEGIN
    DELETE a FROM Playlist a
    INNER JOIN UserPlaylist b ON a.id = b.id AND b.userId = OLD.id;
END$$
DELIMITER ;

Team table:

DELIMITER $$
CREATE TRIGGER team_playlist_delete 
BEFORE DELETE ON Team FOR EACH ROW
BEGIN
    DELETE a FROM Playlist a
    INNER JOIN TeamPlaylist b ON a.id = b.id AND b.teamId = OLD.id;
END$$
DELIMITER ;

What these triggers will do is each time a record is deleted from one of these tables, a DELETE operation will automatically execute on the Playlists table using the id that's about to be deleted (via an inner join).

I have tested this and it works great.

Answer 2

OK I see what you want here... what you want to do is run a query like

DELETE FROM playlist
WHERE       id 
NOT IN      (
    SELECT  id
    FROM    UserPlayList
    UNION
    SELECT  id
    FROM    TeamPlayList
)

after either a row is deleted from either users or teams