Let's say you make a child table that uses email/office as the foreign key and you put in a child with nulls for those values. There's no way to get back to the parent table. Except in SQL Server where NULL is treated as a distinct value rather than an unknown, i.e. null = null -> true. Oracle correctly treats it as an unknown so null = null -> null.
Anyway, my main question was when would you ever have multiple unique keys. In your example above, it's multiple parts of one unique key. I can't think of any situation where you'd have two separate unique keys for a table.
@Webbo - I was taught to use parts of the domain as primary keys, but am coming to understand that this isn't always a good idea, especially if one of those values will be changing regularly. I've shifted over to the auto-increment numeric key with the domain key as a unique key.
say it's a bank, and you have customer checking accounts and ATM / Debit cards.
so each checking account has a different number and each ATM card has a different number but each is a key to the same customer and account balance.
2 Keys one primary the other needed to avoid creating duplicate ATM card numbers.