Lets say a user has a set of data. This data is stored in user_data table. This user_data table is referenced by other users in some way. In case the user wants to delete a row from the user_data table, referential integrity dictates that it cannot be done as long as there exist a reference to that data.
Let me explain further by giving you an example of the type of data that user can store and are referenced by other users - a simplified video streaming service. Some data to be stored:
user_videos
id
video_name
video_url
owner_id
video_playlist
id
name
owner_id
playlist_items
playlist_id
video_id
So from the above example, a user can have videos that can be referenced in playlists which can be created by other users. If referential integrity is enforced, then user won't be able to delete the video unless there are no further references (on delete cascade).
How should I implement the database to handle the deletion?
Option 1: Do not enforce referential integrity. Playlists may refer to videos that do not exist.
Option 2: Hide user video data by introducing a "deleted" field.
Option 3: Assign video to a special user that takes ownership of all orphaned videos.
Option 4: On delete cascade and delete all playlist that references the video.
I'm leaning to towards option 1, but I've always been told to enforce a foreign key constraint. But in the case of interlinking user data as per my example above, could a foreign key be counter intuitive?