PRIMARY KEY and UNIQUE Constraints
Go Up to Defining Integrity Constraints on a Table
The PRIMARY KEY
and UNIQUE
integrity constraints ensure that the values entered into a column or set of columns are unique in each row. If you try to insert a duplicate value in a PRIMARY KEY
or UNIQUE
column, InterBase returns an error. When you define a UNIQUE
or PRIMARY KEY
column, determine whether the data stored in the column is inherently unique. For example, no two social security numbers or driver’s license numbers are ever the same. If no single column has this property, then define the primary key as a composite of two or more columns which, when taken together, are unique.
EMP_NO
|
LAST_NAME
|
FIRST_NAME
|
JOB_TITLE
|
PHONE_EXT
|
---|---|---|---|---|
10335 |
Smith |
John |
Engineer |
4968 |
21347 |
Carter |
Catherine |
Product Manager |
4967 |
13314 |
Jones |
Sarah |
Senior Writer |
4800 |
In the EMPLOYEE
table, EMP_NO
is the primary key that uniquely identifies each employee. EMP_NO
is the primary key because no two values in the column are alike. If the EMP_NO
column did not exist, then no other column is a candidate for primary key due to the high probability for duplication of values. LAST_NAME
, FIRST_NAME
, and JOB_TITLE
fail because more than one employee can have the same first name, last name, and job title. In a large database, a combination of LAST_NAME
and FIRST_NAME
could still result in duplicate values. A primary key that combines LAST_NAME
and PHONE_EXT
might work, but there could be two people with identical last names at the same extension. In this table, the EMP_NO
column is actually the only acceptable candidate for the primary key because it guarantees a unique number for each employee in the table.
A table can have only one primary key. If you define a PRIMARY KEY
constraint at the table level, you cannot do it again at the column level. The reverse is also true; if you define a PRIMARY
KEY constraint at the column level, you cannot define a primary key at the table level. You must define the NOT NULL
attribute for a PRIMARY KEY
column in order to preserve the uniqueness of the data values in that column.
Like primary keys, a unique key ensures that no two rows have the same value for a specified column or ordered set of columns. You must define the NOT NULL
attribute for a UNIQUE
column. A unique key is different from a primary key in that the UNIQUE
constraint specifies alternate keys that you can use to uniquely identify a row. You can have more than one unique key defined for a table, but the same set of columns cannot make up more than one PRIMARY KEY
or UNIQUE
constraint for a table. Like a primary key, a unique key can be referenced by a foreign key in another table.