Let's say I have the following database table and columns:
Event: event_id, event_name School: school_id, school_name Automobile: automobile_id, automobile_name User: user_id, username AssetPermission: user_id, asset_type, asset_id
In the AssetPermission table, the asset_type can take on values like event, school, and automobile, and the asset_id takes the primary key value from rows in event, school or automobile depending on what the asset_type is.
My question is can you apply database constraints (or some other mechanism) to make sure there are no orphan records in the AssetPermission table that might result from deleting records in Event, School and/or Automobile?
I work with either mysql or sql server, so not sure if the technology choice influences the answer.