Depending on what you are modelling I may see a problem for this restriction anyway: for some of our clients the secondary contact address for some people is a shared inbox for the whole team - so we contact the shared box if there is no response (or an out-of-office reply) to messages sent to the personal one.

The only way to enforce the desired restriction declaratively, without using programmed logic such as the trigger method described by Martin, is to treat email addresses as separate entities instead of just properties of a person as you described you had already considered:

Person         EmailAddress    
--------       ----------------
pID (PK)  <--  pID (FK) (PK)   
Name           Type     (PK)   
Blah           Address (unique)

The primary key over person ID and address type enforces (as a primary key implies a unique index) each person having at most one address of each type (primary, secondary) and the unique index on EmailAddress.Address enforces no two people having the same address no matter what address type.

query complexity

This can be addressed using views. You are right that this might introduce "query speed" differences but I'm not sure as they would be significant.

query speed

If this is a significant problem then you could still have primary and secondary address columns in the Person table, and maintain them in using triggers on the EmailAddress table.

You then need to chose what happens if a dev/user tries to update the addresses in the Person table directly: you can either use a trigger to update EmailAddress accordingly, or a trigger to raise an error if the user tries to set values for those columns directly - though in both cases you are trading off insert and update performance for the select speed/convenience. Of course you are now using programmed logic to control things so the complexity is similar to Martin's suggestion.

Another option is indexed views, though I think they are only available on Enterprise Edition which may rule out their use depending on your project's size.

and the probability of a user using multiple email accounts actively decreases in order of magnitude after one

As long as you don't care about that one user who has many and will play marry hell if he can't record them all! Though having said that most systems only accept the possibility of one relevant address anyway.

Depending on what you are modelling I may see a problem for this restriction anyway: for some of our clients the secondary contact address for some people is a shared inbox for the whole team - so we contact the shared box if there is no response (or an out-of-office reply) to messages sent to the personal one.

The only way to enforce the desired restriction declaratively, without using programmed logic such as the trigger method described by Martin, is to treat email addresses as separate entities instead of just properties of a person as you described you had already considered:

Person         EmailAddress    
--------       ----------------
pID (PK)  <--  pID (FK) (PK)   
Name           Type     (PK)   
Blah           Address (unique)

The primary key over person ID and address type enforces (as a primary key implies a unique index) each person having at most one address of each type (primary, secondary) and the unique index on EmailAddress.Address enforces no two people having the same address no matter what address type.

query complexity

This can be addressed using views. You are right that this might introduce "query speed" differences but I'm pretty sure they would not be significant.

query speed

If this is a significant problem then you could still have primary and secondary address columns in the Person table, and maintain them in using triggers on the EmailAddress table.

You then need to chose what happens if a dev/user tries to update the addresses in the Person table directly: you can either use a trigger to update EmailAddress accordingly, or a trigger to raise an error if the user tries to set values for those columns directly - though in both cases you are trading off insert and update performance for the select speed/convenience. Of course you are now using programmed logic to control things so the complexity is similar to Martin's suggestion.

Another option is indexed views, though I think they are only available on Enterprise Edition which may rule out their use depending on your project's size.

This may all be moot, because as I said above: I doubt this structure would make a significant performance difference. I expect it would require a massive data-set for the difference to be reliably measurable, never mind noticeable by end users.

and the probability of a user using multiple email accounts actively decreases in order of magnitude after one

As long as you don't care about that one user who has many and will play merry hell if he can't record them all! Though having said that most systems only accept the possibility of one relevant address anyway.