How to enforce proper subset selection in a list?

Question

As an example: Say there are two tables: Person, and Photo. Each Photo has a PersonId column to indicate who it is a picture of, and each Person can have many Photos. Each Person has a single Photo of themself selected.

In this situation, how can the CurrentlySelected Photo be stored in a relational, denormalized way that ensures the SelectedPhoto doesn't belong to another Person record?

Person   Photo
------   -----
1-John   P1-1(Picture of Person 1, John)
2-Jack   P2-1(John)
         P3-2(Jack... John should not be able to point to select this photo)

How could you ensure -- at the schema level -- that John's row could not point to Photo P3 as a valid SelectedPhoto?

In general, where should the selection from the list be stored? In the master table (Person table in this example)? As an attribute in the Photo table? In a separate table?

Answer 1

Create a column in the Photo table called IsSelected, and create a trigger on this table to ensure IsSelected can only be true for one photo for the same UserID.