RDBMS Ref. Integrity: A child with too many parents?

Question

I have a general design question. Consider these 3 tables:

Table Restaurants:
    RID pk auto_increment
    etc...

Table Vacations:
    VID pk auto_increment
    etc...

Table Movies:
    MID pk auto_increment
    etc...

And now imagine we want to create a list "Top things to do when COVID is over" of selected records from these 3 different tables. The list may contain any mix of records from these tables. What comes to mind then is:

Table Todo:
   Type [ one of R, V, M ]
   ID [ the ID of the parent item ]

But how would you enforce referential integrity on this thing? I.e., how do we ensure that when a restaurant is deleted from Restaurants, it will also drop from Todo?

(I am aware of how to accomplish these things with triggers; Curious if there's a combination of entities that will accomplish this with pure RDBMS ref. int.)

Thank you!

You might want to look at https://stackoverflow.com/questions/4796535/shared-primary-key — Progman, May 12 '20 at 20:27

score 0 · Answer 1 · answered May 12 '20 at 20:25

You can add nullable foreign key columns in your todo table for each target table you have. So your table will look like:

Table Todo:
   RID fk (nullable)
   VID fk (nullable)
   MID fk (nullable)

The type column isn't needed anymore as you can check which column is filled with a foreign key. Obviously you have to add a CHECK constraint to ensure that exactly one foreign key must be set.

RDBMS Ref. Integrity: A child with too many parents?

1 Answers1