Search code examples
databasemodelentityentity-relationshiprelational

Two entities should have same parent entity in relational model

I have simple models: People, Photos, Pair of photos.

An instance of "Pair of photos" should be linked with two "Photo" instances which are linked with the same "Human" instance.

I tried to implement it using ER model. In this case, "PhotoPair" can contain photos from different persons, so it is wrong.

So, how I can solve this problem? (without using triggers)

Solution

  • Since you've got PersonID in PhotoPair, you can set up overlapping foreign key constraints:

    ALTER TABLE PhotoPair
ADD CONSTRAINT 'photo1_person_fk'
    FOREIGN KEY (Photo1, PersonID)
    REFERENCES Photo (PhotoID, PersonID),
ADD CONSTRAINT 'photo2_person_fk'
    FOREIGN KEY (Photo2, PersonID)
    REFERENCES Photo (PhotoID, PersonID);