Primary keys and unique constraints in DB2

Unique constraints

A unique constraint is a rule that the values of a key are valid only if they are unique in the table. The constrained columns are a unique key. Db2 enforces the rule with a unique index during INSERT, UPDATE, MERGE, and the LOAD utility. Every unique key is the key of a unique index.

You define a unique key with the UNIQUE clause of CREATE TABLE or ALTER TABLE. A table can have any number of unique keys, but not two unique constraints on the same set of columns. On Db2 for z/OS the columns of a unique constraint must be NOT NULL. That is stricter than some other database products that allow multiple nulls in a UNIQUE column.

1
2
3
4
5
6
7
8
9
CREATE TABLE HR.EMPLOYEE (
    EMPNO      CHAR(6)      NOT NULL,
    LASTNAME   VARCHAR(30)  NOT NULL,
    EMAIL      VARCHAR(80)  NOT NULL,
    WORKDEPT   CHAR(3),
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO),
    CONSTRAINT UQ_EMP_EMAIL UNIQUE (EMAIL)
  )
  IN HRDB.HRTS;

You can also put UNIQUE on a single column as a column-level shorthand—UNIQUE on column EMAIL is the same idea as UNIQUE (EMAIL) as a table constraint. Name constraints with CONSTRAINT constraint-name so DROP and error messages are readable. If you omit the name, Db2 generates one (often an SQL-prefixed identifier).

LOB types, LONG VARCHAR/VARGRAPHIC, and distinct types based on those types cannot be unique-key columns. Keep unique keys reasonably short: they become index keys, so wide VARCHAR unique keys cost storage and CPU on every insert.

UNIQUE versus a unique index you create yourself

CREATE UNIQUE INDEX also prevents duplicates. A UNIQUE constraint is the catalog-level rule; the index is the enforcement mechanism. Prefer declaring UNIQUE (or PRIMARY KEY) when the uniqueness is a business rule, so referential integrity can point at it and so the intent is visible in SYSIBM.SYSTABCONST / SYSIBM.SYSKEYS. A unique index alone is still valid for access paths and uniqueness, but it is not the same object as a named unique constraint.

UNIQUE WHERE NOT NULL on CREATE INDEX is a different option: it allows duplicate nulls while enforcing uniqueness among non-null keys. That is not how a UNIQUE constraint works on z/OS, because constraint columns cannot be null.

PRIMARY KEY

A primary key is a special unique key that cannot contain null values. Example: DEPTNO on the department table. Rules that beginners must memorize:

• At most one primary key per table
• Primary keys are optional in the engine—Db2 will store a heap-like table without one—but almost every real table should have one
• Define them on CREATE TABLE or later with ALTER TABLE
• The unique index on a primary key is the primary index. Db2 creates it automatically when you define the primary key
• If a unique index already exists on those columns when you ALTER TABLE ADD PRIMARY KEY, Db2 can designate that index as the primary index in the situations IBM documents (including some implicitly created table space cases)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Column-level shorthand for a single-column primary key
CREATE TABLE HR.DEPT (
    DEPTNO    CHAR(3)      NOT NULL PRIMARY KEY,
    DEPTNAME  VARCHAR(36)  NOT NULL,
    MGRNO     CHAR(6)
  )
  IN HRDB.DEPTTS;
 
-- Table-level form (required for multi-column keys)
CREATE TABLE ORD.ORDLINE (
    ORDNO     INTEGER      NOT NULL,
    LINENO    SMALLINT     NOT NULL,
    ITEMID    INTEGER      NOT NULL,
    QTY       INTEGER      NOT NULL,
    CONSTRAINT PK_ORDLINE PRIMARY KEY (ORDNO, LINENO)
  )
  IN ORDDB.OLTS;

A composite primary key (ORDNO, LINENO) is still one primary key, not two. The uniqueness is on the combination: two lines can share an order number; two rows cannot share both order number and line number.

PRIMARY KEY versus UNIQUE

Pick the primary key as the stable identifier other tables will reference. Put UNIQUE on extra natural keys. Do not make a wide descriptive VARCHAR the primary key if a compact CHAR/INTEGER surrogate is what COBOL programs already use as EMPNO.

Adding and dropping with ALTER TABLE

1
2
3
4
5
6
7
8
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO);
 
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT UQ_EMP_EMAIL UNIQUE (EMAIL);
 
ALTER TABLE HR.EMPLOYEE
  DROP CONSTRAINT UQ_EMP_EMAIL;

Adding a primary key or unique constraint on a table that already contains duplicates fails until you clean the data. Adding a constraint on a populated table also means Db2 must build (or validate) the unique index—plan for sort work and availability. Dropping a primary key that is still a parent of foreign keys is rejected until those RI constraints are dropped or changed.

Parent keys and referential integrity

A unique constraint that a foreign key references is a parent key. The primary key is the usual parent key, but a UNIQUE constraint can be a parent key too if the FOREIGN KEY points at those columns. Child rows cannot exist without a matching parent key value (unless the foreign key is null, when the child columns allow nulls). Design the primary key first, then foreign keys—not the other way around.

Enforcement and application errors

Duplicate-key failures surface as negative SQLCODEs in the program (classically -803). LOAD with ENFORCE CONSTRAINTS (or equivalent shop standards) also checks unique keys. Disable or deferred checking is a utility/availability topic, not a reason to skip declaring the constraint. Applications should treat duplicate key as a business error (“employee number already exists”), not as a crash.

Clustering is independent: the primary index is unique; it may or may not be the CLUSTER index. Many shops make the primary index the clustering index; others cluster on a different access pattern (for example WORKDEPT) and keep EMPNO unique but not clustering.

Explain It Like I'm Five

A unique constraint is a classroom rule: no two backpacks may have the same name tag. A primary key is the special name tag the teacher uses to call roll—there is only one official tag per backpack, and it cannot be blank. Db2 keeps a little alphabetized list (the unique index) so it can instantly shout “that tag is already taken” when someone tries to copy it.

Exercises

1. Write CREATE TABLE for a CUSTOMER table with CUSTNO CHAR(8) as primary key and EMAIL VARCHAR(80) as UNIQUE.
2. Explain why EMPNO CHAR(6) PRIMARY KEY must also be NOT NULL.
3. Write a composite PRIMARY KEY for order lines on (ORDNO, LINENO).
4. Describe what Db2 builds when you ADD PRIMARY KEY, and what happens if duplicates already exist.
5. Give one reason to have both a primary key and a UNIQUE constraint on the same table.

Frequently asked questions