0

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.

|improve this question
  • You want to look into Foreign Keys and Cascading – LowlyDBA Mar 20 '15 at 23:18
  • @John Thanks John, can you show a working example that will work on the AssetPermission junctin table above? I've never seen it done on a junction table where the FK is not strictly constrained to one entity type – John Mar 23 '15 at 13:47
  • I'd probably use a single table for Event, School, and Automobile since they all share the same columns and you're already differentiating the type of asset in AssetPermission (though asset type should be a reference table of its own). This simplifies your constraint to just the two tables. – LowlyDBA Mar 23 '15 at 13:59
  • @John Thanks john. Assume for the moment I can't use a single table for all the different entity types. That I have to do what i did above, and now i just need to find a way to apply some kind of constraint that is entity type dependent in the AssetPermission table. Is that possible? – John Mar 23 '15 at 14:06
  • 1
    Check this answer on SO for a better idea. The trigger contains whatever logic you want, so yes it can check if a parent record exists. – LowlyDBA Mar 23 '15 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.