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:
![enter image description here](https://i.stack.imgur.com/TVhag.jpg)
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 & RoleId WHERE the RoleIds are those that can only have one per team. I haven't tried it though.
Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key.
A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally used as a primary key (I never really understood why one of the c.k. is concidered "better" than the others, but that's another story), and the remaining c.k becomes unique constraints.
A unique constraint can be used the same way as a primary key can. Consider:
create table A ( x ... not null
, y ... not null
, z ... not null
, unique (x)
, primary key (y,z) );
create table B ( x ...
, ...
, foreign key (x) references A (x) );
create table C ( y ...
, z ...
, ...
, foreign key (y, z) references A (y, z) );
B references the unique constraint and C references the primary key constraint.
NOT NULL is yet another kind of constraint. In your case you can enforce this for email without declaring it unique.
The next aspect of your post concerns the stability of a key, a key should be stable (but that doesn't mean it can never change, it does not have to be immutable). Some DBMS implements ON UPDATE CASCADE that can be of help for such operation, still if the key is distributed around your model it will be a pain updating it.
In your case I would probably choose another candidate key as the primary key, and declare email to be NOT NULL and UNIQUE.
Best Answer
To enforce unique email addresses, I would remove all competing email columns and store them in one central
email
table for all active emails. And another table for deleted emails:This way:
email
.email
toemail_deleted
.users_primary_email_fkey
to span(user_id, email)
, which seems redundant at first. But this way the primary email can only be an email that is actually owned by the same user.Due to the default
MATCH SIMPLE
behavior of FK constraints, you can still enter a user without primary email, because the FK constraint is not enforced if any of the columns is null.Details:
The
UNIQUE
constraint onusers.email
is redundant for this solution, but it may be useful for other reasons. The automatically created index should come in handy (for instance for the last query in this answer).The only thing that's not enforced this way is that every user has a primary email. You can do this, too. Add
NOT NULL
constraint tousers.email
UNIQUE (user_id, email)
is required for the FK constraint:You have doubtless spotted the circular reference in the above model. Contrary to what one might expect, this just works.
As long as
users.email
can beNULL
, it's trivial:INSERT
user without email.INSERT
email referencing the owninguser_id
.UPDATE
user to set it's primary email if applicable.It even works with
users.email
set toNOT NULL
. You have to insert user and email at the same time though:IMMEDIATE
FK constraints (the default) are checked at the end of each statement. The above is one statement. That's why it works where two separate statements would fail. Detailed explanation:To get all emails of a user as array, with the primary email first:
You could create a
VIEW
with this for ease of use.LATERAL
requires Postgres 9.3. use a correlated subquery in pg 9.2:To soft-delete an email:
To soft-delete the primary email of a given user:
Details:
Quick test for all of the above: SQL Fiddle.