Alternate Keys






Alternate Keys

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.)

CREATE   TABLE TEAMS
        (TEAMNO     INTEGER NOT NULL,
         PLAYERNO   INTEGER NOT NULL UNIQUE,
         DIVISION   CHAR(6) NOT NULL,
         PRIMARY KEY (TEAMNO))

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.

Exercise 16.4:

Indicate what is incorrect in the following CREATE TABLE statements.

  1. CREATE TABLE T1
          (C1  INTEGER NOT NULL,
           C2  INTEGER NOT NULL UNIQUE,
           C3  INTEGER NOT NULL,
           PRIMARY KEY (C1, C4))
    

  2. CREATE TABLE T1
          (C1  INTEGER NOT NULL PRIMARY KEY,
           C2  INTEGER NOT NULL,
           C3  INTEGER UNIQUE,
           PRIMARY KEY (C1))
    

  3. CREATE TABLE T1
          (C1  INTEGER NOT NULL PRIMARY KEY,
           C2  INTEGER NOT NULL,
           C3  INTEGER UNIQUE,
           UNIQUE (C2, C3))
    



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows