Unique Constraint and Primary Key Constraint

Unique Constraint
A unique constraint requires that every value in a column be unique. That is, no two rows can have duplicate values in a specified column or combination of columns. Choose columns for unique constraints carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. In the employees table, the email column has a unique key constraint because it is important that the e-mail address for each employee is unique. Note that the email column has a NOT NULL constraint.Some examples of good unique keys include:

■ An employee social security number, where the primary key might be the employee number

■ A truck license plate number, where the primary key might be the truck number

■ A customer phone number, consisting of the two columns area_code and local_phone, where the primary key might be the customer number

■ A department name and location, where the primary key might be the department number Primary Key Constraint A primary key requires that a column (or combination of columns) be the unique identifier of the row and ensures that no duplicate rows exist. A primary key column cannot contain NULL values. Each table can have only one primary key. Use the following guidelines when selecting a primary key:

■ Whenever practical, create a sequence number generator to generate unique numeric values for your primary key values. Choose a column whose data values are unique, because the purpose of a primary

key is to uniquely identify each row of the table. 

■ Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed. Choose a column that does not contain any null values. A PRIMARY KEY

constraint, by definition, does not allow any row to contain a null value in any column that is part of the primary key.

■ Choose a column that is short and numeric. Short primary keys are easy to type.

■ Minimize your use of composite primary keys. A composite primary key constraint applies to more than one column. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence

numbers.