-1

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)

Sh Svyatoslav
  • 359
  • 4
  • 12
  • Firstly - remove the foreign key from person to photo_pair. You already have implied that there ~can be~ more than one person based on the implications from photo's two contributions. Unless there are actually 3 photo owners in each pair. Secondly, there is no way based purely on constraints to say that the persons "owning" the individual photos in photo_pair are different. I'm actually not sure why you are modelling this way to begin with. Are there ALWAYS ONLY going to be "pairs" of photos? – T Gray Apr 15 '16 at 16:07
  • Thank you for your reply. There always will be pairs of photos. – Sh Svyatoslav Apr 25 '16 at 17:49

1 Answers1

1

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);
reaanb
  • 9,806
  • 2
  • 23
  • 37