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.

learningtech
  • 189
  • 4
  • 12
  • You want to look into Foreign Keys and Cascading – LowlyDBA - John M 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 – learningtech 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 - John M 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? – learningtech Mar 23 '15 at 14:06
  • To add the constraint across all the tables, you'd have to write your own trigger to perform the checking logic. It wouldn't be very efficient and I'd recommend against that route, though, if you can change it at all. It'll differ slightly between SQL Server and MySQL so check the documentation for each. – LowlyDBA - John M Mar 23 '15 at 14:12
  • Thanks John. Will triggers prevent orphan records in the asset permission table? Or what if a person decides to enter a record into the asset permission table with out ever entering a corresponding parent record in one of those entities? My understanding is that triggers won't protect against this, did I miss understand something? – learningtech Mar 23 '15 at 15:34
  • 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 - John M Mar 23 '15 at 15:36

0 Answers0