SQL Performance Tuning [Electronic resources] نسخه متنی

PRIMARY KEY

A PRIMARY KEY constraint is used to enforce a rule that a column may contain only unique, non-NULL data. This is to ensure that each row of a table can be uniquely identified. Too frequently, you will encounter the wrong sort of primary-key specification:

CREATE TABLE Table1 (
column1 ... NOT NULL PRIMARY KEY,
...)

No gross harm results, but that's not the usual recommendation. To preserve orthogonality, this definition is often suggested instead:

CREATE TABLE Table1 (
column1 ... NOT NULL,
CONSTRAINT Constraint1 PRIMARY KEY (column1),
...)

We'll go a little further and askIs even that specification wrong? After all, primary-key columns can't contain NULL by definition, so specifying NOT NULL with standard SQL is a redundancy. Again, we couldn't find any evidence that gross harm results, but we'd still say that the only fully correct specification for a primary key is:

CREATE TABLE Table1 (
column1 ... ,
CONSTRAINT Constraint1 PRIMARY KEY (column1),
...)

Unfortunately, five of the Big EightIBM, Ingres, InterBase, MySQL, and Sybasewon't allow it (see Table 10-1).

In the SQL:1999 SQL/Foundation document, all tables are defined to have a primary key via functional dependencies, but this area is a little-known and complicated one that no DBMS has implemented. So everyone recommends that an explicit PRIMARY KEY definition should appear for every table defined. Everyone is rightbut when you know that there will never be duplicates or foreign keys (e.g., with a one-row temporary table), making a primary key is unnecessary and costly.

In a reasonable world, you would be able to add the primary key and any matching foreign keys after the table exists. Adding the foreign key after table creation would solve the cyclic references problem. For example, the two table definitions shown in Listing 10-1 are illegal outside a CREATE SCHEMA statement. The elegant solution to the cyclic references problem is shown in Listing 10-2.

Listing 10-1 Illegal cyclic references table definitions

CREATE TABLE Table1 (
column1 INTEGER PRIMARY KEY,
column2 INTEGER FOREIGN KEY REFERENCES Table2)
CREATE TABLE Table2 (
column2 INTEGER PRIMARY KEY,
column1 INTEGER FOREIGN KEY REFERENCES Table1)

Listing 10-2 Solving the cyclic references problem

CREATE TABLE Table1 (
column1 INTEGER,
column2 INTEGER)
CREATE TABLE Table2 (
column2 INTEGER,
column1 INTEGER)
ALTER TABLE Table1
ADD CONSTRAINT Constraint1
PRIMARY KEY (column1)
ALTER TABLE Table2
ADD CONSTRAINT Constraint2
PRIMARY KEY (column2)
ALTER TABLE Table1
ADD CONSTRAINT Constraint3
FOREIGN KEY (column2) REFERENCES Table2
ALTER TABLE Table2
ADD CONSTRAINT Constraint4
FOREIGN KEY (column1) REFERENCES Table1

A simple one-user shop should use ascending integers for a primary key.

Second of all is the fact that a serial sequence can cause hot spots. Hot spots are pages in either the index or data file that every job wants to access at the same time. Consider: If Sam and Mary are both adding rows to the same table, and Sam's inserted row is #5550001, then Mary's row will be #5550002because that's the next in sequence and index keys are in sequence. So it's a near certainty that both Sam and Mary will want to lock the same index page. There are ways around the hot spot problem, such as reverse-key indexing and range locking; see Chapter 9, "Indexes," and Chapter 15, "Locks." But the simpler solution is to avoid hot spots entirely. Therefore:

A complex multiuser shop should use an out-of-order series of characters for a primary key. This key can be derived from (a) the time of day to the nearest millisecond or (b) the user's session identifier. Such keys are often defined as CHAR(12) or longer.

So far, our advice has assumed that you will be able to assign arbitrary values to your primary keys. Sadly, the easiest way to make arbitrary values unique is to use a serial sequence.

Here are other rules of thumb for defining primary keys:

The primary-key column should be the first column in the table.

The value should/should not have any meaning.

But these rules are mere conventions. What counts is that the primary-key column should be easy to form and assuredly unique. It should also, preferably, be a value that is very rarely changed.

The Bottom Line: PRIMARY KEY Constraints

A primary key is a unique, non-NULL column, or group of columns, that uniquely identifies a row of a table.

If you're programming for a simple one-user shop, use ascending integers for primary-key columns.

If you're programming for a complex multiuser shop, avoid hot spotsuse an out-of-order series of characters derived from the time of day to the nearest millisecond or the user's session identifier for primary-key columns.

When defining a primary key, remember the most important criterion: the column chosen should be easy to form and assuredly unique.