Primary Keys






Primary Keys

A primary key is (informally) known as a column or group of columns of a table of which the values are always unique. NULL values are not permitted in columns that form part of a primary key. In the example in Section 15.2, in Chapter 15, the column PLAYERNO is defined as the primary key of the PLAYERS table.

Primary keys can be defined in two ways: as column or table integrity constraints. In the first case, the term PRIMARY KEY is simply added to the column definition.

Figure. Create the PLAYERS table, including the primary key.

CREATE TABLE PLAYERS (
       PLAYERNO     INTEGER NOT NULL PRIMARY KEY,
       :            :
       LEAGUENO     CHAR(4))

Explanation: The primary key is defined after the null specification. The null specification may be specified behind the primary key.

In this example, we can also define the primary key as a table integrity constraint:

CREATE TABLE PLAYERS (
       PLAYERNO     INTEGER NOT NULL,
       :            :
       LEAGUENO     CHAR(4),
       PRIMARY KEY  (PLAYERNO))

You can define primary keys over multiple columns in a table. These are called composite primary keys. The COMMITTEE_MEMBERS table contains such a composite primary key. A composite primary key can be defined as only a table integrity constraint. All relevant columns are placed between brackets.

Figure. Create a DIPLOMAS table to record, among other things, which course members followed which course on which date; the STUDENT, COURSE, and DDATE columns will form a composite primary key.

CREATE   TABLE DIPLOMAS
        (STUDENT     INTEGER NOT NULL,
         COURSE      INTEGER NOT NULL,
         DDATE       DATE NOT NULL,
         SUCCESSFUL  CHAR(1),
         LOCATION    VARCHAR(5),
         PRIMARY KEY (STUDENT, COURSE, DDATE))

Explanation By defining the primary key on the three columns, you can ensure that a student can obtain only one diploma for only one course on a specific date.

If a column that is part of a primary key has not been defined as NOT NULL, SQL defines the column as NOT NULL. The specification NOT NULL in the PLAYERNO column in the previous examples can be left out; however, we do not recommend that. For the sake of clarity, it is better to include this null specification.

Portability

Some SQL products handle the NULL differently if a column is part of a primary key. In those products, if a primary key column has not been defined as NOT NULL, they do not endorse the CREATE TABLE statement. In this case, each primary key column must explicitly be defined as NOT NULL.


Any column or group of columns can, in principle, function as a primary key. Nevertheless, primary key columns must follow a number of rules. Some of these rules stem from the theory of the relational model; others are enforced by SQL. We advise you to follow these rules when you define primary keys:

  • Only one primary key can be defined for each table. This rule comes from the relational model and applies to SQL as well.

  • The theory (the relational model) requires that one primary key should be defined for each table. SQL, however, does not enforce this; you can create tables without a primary key. However, we strongly recommend that you specify a primary key for each base table. The main reason is that, without a primary key, it is possible (accidentally or deliberately) to store two identical rows in a table; as a result, the two rows no longer would be distinguishable from one another. In query processes, they will satisfy the same conditions, and in updating, they will always be updated together, so there is a high probability that eventually the database will become corrupted.

  • Two different rows in a table may never have the same value for the primary key. In the literature, this is called the uniqueness rule. As an example, the TOWN column in the PLAYERS table should not be specified as a primary key because many players live in the same town.

  • A primary key is not correct if it is possible to delete a column from the primary key and have this "smaller" primary key still satisfy the uniqueness rule. This rule is called the minimality rule. In short, this means that a primary key should not consist of an unnecessarily high number of columns. Imagine that we would define PLAYERNO with NAME as the primary key for the PLAYERS table. We already know that player numbers are unique, so, in this case, the primary key contains more columns than necessary and, therefore, does not satisfy the minimality rule.

  • A column name may occur only once in the column list of a primary key.

  • The populations of the columns belonging to a primary key may not contain NULL values. This rule is known either as the first integrity constraint or as the entity integrity constraint. What would happen if we allowed NULL values in a primary key? It would be possible to insert two rows with NULL values as the primary key values and other columns with identical data. These two rows would not be uniquely identifiable and would always satisfy the same conditions for selection or updating. You cannot infringe this rule because SQL requires that the columns concerned be defined as NOT NULL.

Exercise 16.1:

Do you have to specify a NOT NULL integrity constraint for a column defined as the primary key?

Exercise 16.2:

What is the minimum and maximum number of primary keys that can be defined for each table?

Exercise 16.3:

Define the primary key for the MATCHES table.



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