How to enforce referential integrity on Single Table Inheritance?
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)
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.
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.
What you can do is implement triggers on your Users and Team tables that execute whenever rows get deleted from either:
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 ;
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.
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
The answer by Zane Bien is quite obvious & superb.But I have an idea for doing this without use of trigger because trigger has many problems.
Are you using any programming language ? If yes then,
Use a single transaction and make your database auto commit false
write a delete query for the referenced rows in Playlist and PlaylistVideo . Manually you have to write this query first by using that reference id(with where condition) and run it.
Now prepare another query for your main task i.e. delete the User, and the rows in UserPlaylist will be deleted automatically ( due to CASCADE DELETE option).Now run your second query and commit.
Finally make your transaction auto commit true.
It is working successfully, hope it will helpful.