Unique column values between tables
If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:



By putting the TeamId in the People table you enforce the fact that a Person can only be on one team.  You then have a many:many relationship using a cross join table between People and Roles.  This allows a person to have multiple roles.  By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role.  You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact.  You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId &amp; RoleId WHERE the RoleIds are those that can only have one per team.  I haven't tried it though.