In the relational model, an alternate key is, like a primary key, a column or group of columns of a table, of which the values are unique at all times. Chapter 1 indicates that an alternate key is a candidate key that is not chosen to be the primary key. There are two important distinctions between primary and alternate keys. First, a table may have many alternate keys but only one primary key. Second, according to the theory of the relational model, primary keys cannot contain NULL values, whereas alternate keys can (unless it is explicitly forbidden with a NOT NULL integrity rule). However, SQL also follows the rule that alternate keys can never contain NULL values.
Figure. Define the PLAYERNO column in the TEAMS table as an alternate key. (We assume in this example that a player may captain only one team.)
Explanation The word UNIQUE indicates that PLAYERNO is an alternate key and that the values must remain unique.
The previous statement could also have been defined as follows. The alternate key is defined as table integrity constraint:
CREATE TABLE TEAMS (TEAMNO INTEGER NOT NULL, PLAYERNO INTEGER NOT NULL, DIVISION CHAR(6) NOT NULL, PRIMARY KEY (TEAMNO), UNIQUE (PLAYERNO))
Each table can have several alternate keys, and they may even overlap. We can define one alternate key on the columns C1 and C2, and another on C2 with C3. There is overlap on the C2 column, then, which SQL allows. Alternative keys may also overlap with the primary key. However, it makes no sense to define a set of columns as an alternate key when that set is a superset of the columns of another key. If a primary key has been defined, for example, on the column C1, the definition of an alternate key on the columns C1 and C2 is unnecessary. The uniqueness of the combination C1, C2 is already guaranteed by the primary key. However, SQL allows this construct, so be careful that you do not make mistakes.