Primary key with two references

I would like that my relation table “In” has a Foreign key SSN which references to two tables, but the SSN is only in one of these two tables. When I do this:

ALTER TABLE "In"
ADD CONSTRAINT in_C1 FOREIGN KEY (SSN) REFERENCES patient(SSN),
ADD CONSTRAINT in_C2 FOREIGN KEY (SSN) REFERENCES patientWithDoD(SSN)

Then the SSN must be in the table patient and patientWithDoD. The SSN should be in one of the two tables, but not both. How can I do this?

Answer

When a column has a non-null value all related foreign key constraints are checked and validated. If the check fails, the row cannot be inserted or updated.

If you have two FKs and you want only one of them to be enforced, then you’ll need to:

• create two separated nullable columns, one for each FK, and
• add a CHECK constraint to enforce one and only one is not null.

For example:

create table "In" (
  id int primary key not null,
  ssn int, -- nullable
  ssn_dod int, -- nullable
  constraint fk1 foreign key (ssn) references patient (ssn),
  constraint fk2 foreign key (ssn_dod) references patientwithdod (ssn),
  constraint chk1 check (ssn is null and ssn_dod is not null or
                         ssn is not null and ssn_dod is null)
);

Unfortunately, MySQL just recently started to enforce the CHECK constraints (MySQL 8.0.3 if I remember well). If you are using an older version of MySQL, you are out of luck: MySQL will allow you to write the constraint and save it, but won’t enforce it.