The other "unique" constraints are often called "Alternate Keys" (AK in the IDEF1X data modeling notation).
Consider the following simple example:
Countries, per the ISO 3166 specification can be identified by:
- Name (e.g., "United States of America")
- 2-character code (e.g., "US")
- 3-character code (e.g., "USA")
- 3-digit number (e.g., "840")
Each of which must be unique. So a table of Countries would have one Primary Key (PK) and at least 2 other alternate keys (unique constraints) that can provide indexed lookups via the other "key" values.
Which one to use as the primary key would depend upon the application. But, in this example case, I'd use the 3-character code as the primary key (as it is the most common usage).
(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.