How can I enforce data integrity in this model with a M:2 relationship?

Question

This model describes a part of my database (somewhat simplified):

A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 mouse. "Experimental animal" is the specific animal from a given line which is used for something in an experiment. Each animal can have tumor entities, and the researcher can do interventions on it (e.g. give the animal chemotherapy). So far, pretty straightforward.

The part where I ran into problems was when I had to add a transplantation. A transplantation is always performed on exactly two animals within the same tumor model, one being the donor and the other the acceptor. There can be multiple transplantations within a tumor model. As it is a M:2 and not a M:N relationship, I took a shortcut and did not make a junction table out of the "performed on" relationship, opting instead for two foreign keys from the Experimental animal table to be referenced in the Transplantation table.

My problem is that now, I am able to create a transplantation between two animals which are not part of the same model. (By the way, using a junction table instead of two IDs in Transplantation would not have removed the problem). I tried many ways to model the whole thing and I am quite sure that there is no way to enforce this on the ERD model level. I know that we can enforce this well within the business logic, but our department has a "our user has a one-time wishes to do something, we will quickly throw together a script/Access frontend/whatever to do it" policy, where the business logic gets completely bypassed.

What are my options for ensuring that I never get a Transplantation row with two IDs pointing to experimental animals from two different models? Keep in mind that as an application developer, I do not have experience with databases beyond creating my own schema and doing CRUD operations in SQL. I am not sure if I can somehow put together a constraint which can do what I want, or maybe set a trigger which prevents updates and inserts with the wrong combination of IDs, or if there is something else entirely I didn't think of. Ideas about which way leads to the cleanest solution and a rough description of what exactly I would need to implement are welcome.

Answer 1

You can add a "model" attribute to Transplantation and use it (in combination with the Donor and Receiver IDs) to create the 2 foreign keys.

If the "model" attribute is not part of Experimental Animal but of Used In, make the foreign keys target that relation.

In SQL terms that would be something like:

CREATE TABLE Transplantation
( DonorID
, ReceiverID
, ModelID
-- other attributes

-- primary key and other constraints    

, CONSTRAINT Donor_FK
    FOREIGN KEY (ModelID, DonorID)
    REFERENCES UsedIn                        -- UsedIn or ExperimentalAnimal
       (ModelID, ExperimentalAnimalID)       -- depending where the model 
, CONSTRAINT Receiver_FK                     -- attribute resides
    FOREIGN KEY (ModelID, ReceiverID)
    REFERENCES UsedIn                        -- same as above
      (ModelID, ExperimentalAnimalID)
, CONSTRAINT CommonSense_CK
    CHECK (DonorID <> ReceiverID)
) ;

The above FKs to be created, a UNIQUE constraint on UsedIn(ModelID, ExperimentalAnimalID) is required.