“Multiple” Foreign Key

I have tables:

• MUSICIANS (musician_id, …)
• PROGRAMMERS (programmer_id, …)
• COPS (cop_id, …)

Then I’m going to have a specific table

• RICH_PEOPLE (rich_person_id, …)

where rich_person_id is either musician_id, programmer_id or cop_id. (Assume that all the musician_ids, programmer_ids, cop_ids are different.)

Is it possible to directly create a Foreign Key on the field rich_person_id?

P.S. I would like the database to

• ensure that there is a record of either MUSICIANS, PROGRAMMERS or COPS with the same id as the new RICH_PEOPLE record’s rich_person_id before inserting it into RICH_PEOPLE
• deleting from either MUSICIANS, PROGRAMMERS or COPS would fail (or require cascade deletion) if there a RICH_PEOPLE record with the same id

P.P.S. I wouldn’t like

• creating an extra table like POSSIBLY_RICH_PEOPLE with the only field possibly_rich_person_id
• creating triggers

Answer

You can create three nullable foreign keys, one to each foreign table. Then use a CHECK constraint to ensure only one value is not null at any given time.

For example:

create table rich_people (
  rich_person_id int primary key not null,
  musician_id int references musicians (musician_id),
  programmer_id int references programmers (programmer_id),
  cop_id int references cops (cop_id),
  check (musician_id is not null and programmer_id is null and cop_id is null
      or musician_id is null and programmer_id is not null and cop_id is null
      or musician_id is null and programmer_id is null and cop_id is not null)
);

This way, referential integrity will be ensured at all times. Deletions will require cascade deletion or other strategy to keep data integrity.