Alternate keys as unique constraints

Suppose we were modeling an EMPLOYEE table that contains an EMPLOYEE_ID column—a unique business identifier—and Social Security numbers—government-issued personal identifiers. Either column would satisfy the PK requirement of uniquely identifying a record in the EMPLOYEE table, but recall that a table may only be assigned one PK. To let database users know that another column (or columns) satisfies the conditions for a PK when a PK already exists, alternate keys (AKs) or UNIQUE constraints can also be defined.

In the previous example, the EMPLOYEE table had two valid PK candidates: EMPLOYEE_ID and SOCIAL_SECURITY_ID. In OLTP databases, the column or columns that act as the organizational business key should be made the primary. In a data warehouse, where business keys from multiple source systems may be loaded, a surrogate key would be used instead. By this convention, the EMPLOYEE table should be modeled with EMPLOYEE_ID...