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

In my view, the problem is that your User and Team tables are the ones that should have a supertype table (such as Party), not the Playlist tables.

As you've pointed out, doing your "table inheritance" on playlists comes with penalties when trying to figure out what to delete. All those problems go away when you move the inheritance up to the user/team level.

You can see this answer for more detail about supertyping/subtyping.

I'm sorry to not supply code as I don't know the MySQL syntax by heart.

The basic concept is that the supertype table allows you to implement a database kind of polymorphism. When the table you're working with needs to link to any one of a group of subtypes, you just make the FK point to the supertype instead, and this automatically gets you the desired "only a one of these at a time" business constraint. The super type has a "one-to-zero-or-one" relationship with each of the subtype tables, and each subtype table uses the same value in its PK as the PK from the supertype table.

In your database, by having just one Playlist table with an FK to Party (PartyID), you have easily enforced your business rule at the database level without triggers.