DB2 add primary key

What a primary key means

A primary key implements entity integrity. In plain language, every row represents one identifiable thing, and that identity is never missing or repeated. For an employee table, EMPNO might identify one employee. For an order-line table, neither ORDER_ID nor LINE_NO is sufficient by itself, but the pair identifies one line within one order. DB2 records this business rule as a table constraint and enforces it whenever data is inserted, updated, or loaded with constraint enforcement.

Primary key, UNIQUE constraint, and unique index are related but not identical. The primary key is the table's chosen identity and can be referenced by foreign keys. A UNIQUE constraint describes another candidate key. A unique index is the physical access structure DB2 uses to prevent duplicate values. One table may have several unique indexes and UNIQUE constraints, but it can have only one primary key.

Start by inspecting the existing data

Never assume that a column named ID is safe. Older applications may have permitted nulls, reused default values, or generated identifiers only for newer rows. First count rows whose candidate key is incomplete. For a single-column key, test that column. For a composite key, test every component because one null makes the proposed identity incomplete.

1
2
3
4
5
6
7
8
9
10
-- Candidate key: APP.CUSTOMER(CUSTOMER_ID)
SELECT COUNT(*) AS NULL_KEY_ROWS
FROM APP.CUSTOMER
WHERE CUSTOMER_ID IS NULL;
 
-- Candidate composite key: APP.ORDER_LINE(ORDER_ID, LINE_NO)
SELECT COUNT(*) AS NULL_KEY_ROWS
FROM APP.ORDER_LINE
WHERE ORDER_ID IS NULL
   OR LINE_NO IS NULL;

Next find duplicates. Group by the complete candidate key, count each group, and retain only counts greater than one. Do not check composite columns independently. Many order lines can correctly share ORDER_ID, and many orders can correctly contain LINE_NO 1; only the combination must be unique.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Single-column duplicate inspection
SELECT CUSTOMER_ID, COUNT(*) AS ROW_COUNT
FROM APP.CUSTOMER
GROUP BY CUSTOMER_ID
HAVING COUNT(*) > 1
ORDER BY ROW_COUNT DESC;
 
-- Composite-key duplicate inspection
SELECT ORDER_ID, LINE_NO, COUNT(*) AS ROW_COUNT
FROM APP.ORDER_LINE
GROUP BY ORDER_ID, LINE_NO
HAVING COUNT(*) > 1
ORDER BY ROW_COUNT DESC;

Treat every result as a business-data issue, not merely a DDL obstacle. A duplicate might represent accidental double loading, two legitimate entities that need a better key, or a historical row that should be retained with a version number. Decide whether to merge, delete, renumber, archive, or redesign with the data owner. Keep evidence of row counts before and after remediation, and rerun both null and duplicate checks immediately before deployment.

Make candidate columns NOT NULL

Having no null values today does not make a nullable column eligible forever. Its catalog definition must prevent future nulls. After confirming the data and application impact, alter each proposed key column to NOT NULL using syntax supported by your DB2 level and local change process. Existing INSERT statements that omit the column, host programs that supply a negative null indicator, and LOAD jobs that contain null fields must be corrected before enforcement begins.

1
2
3
4
5
6
7
8
ALTER TABLE APP.CUSTOMER
  ALTER COLUMN CUSTOMER_ID SET NOT NULL;
 
ALTER TABLE APP.ORDER_LINE
  ALTER COLUMN ORDER_ID SET NOT NULL;
 
ALTER TABLE APP.ORDER_LINE
  ALTER COLUMN LINE_NO SET NOT NULL;

Depending on the existing definition, table-space organization, DB2 level, and exact ALTER operation, DB2 can require validation or follow-up utility work. Test the same DDL against a production-like copy and record any object status change. Do not proceed to the constraint merely because the ALTER statement returned successfully; verify that the catalog now shows each key column as not nullable.

Add the primary key constraint

The standard form names the constraint and lists the key columns. A meaningful constraint name makes messages, catalog queries, and future changes easier to understand. Naming conventions vary, but names such as PK_CUSTOMER and PK_ORDER_LINE clearly communicate the purpose.

1
2
3
4
5
6
7
ALTER TABLE APP.CUSTOMER
  ADD CONSTRAINT PK_CUSTOMER
  PRIMARY KEY (CUSTOMER_ID);
 
ALTER TABLE APP.ORDER_LINE
  ADD CONSTRAINT PK_ORDER_LINE
  PRIMARY KEY (ORDER_ID, LINE_NO);

DB2 validates the definition and establishes unique index enforcement. If the data or column definitions violate the rule, the operation fails or requires corrective handling according to the reported condition. Do not respond by disabling checks or deleting rows blindly. Read the SQLCODE and messages, identify the exact key or object state, correct the underlying cause, and repeat the prechecks.

Composite key order is a design decision

In PRIMARY KEY (ORDER_ID, LINE_NO), the sequence is ORDER_ID first and LINE_NO second. Reversing the sequence still tests uniqueness across the same pair, but it creates a different ordered index and a different key sequence in the catalog. ORDER_ID first is normally useful when applications retrieve all lines for one order, because matching the leading index column supports that access path. LINE_NO first would organize entries primarily by line number across orders and might not support that common query as well.

Choose order from the entity relationship and important access paths, not alphabetical preference. Also remember that foreign keys referencing the primary key normally use the corresponding parent-column sequence. Changing primary-key order later is a coordinated schema change involving indexes, dependent constraints, applications, packages, and utilities, so make the decision deliberately.

The unique primary index

DB2 enforces the primary key through a unique index. When no suitable index exists, DB2 can create the required unique primary index as part of establishing the constraint. That automatic behavior is convenient, but the generated index might not use the naming, buffer pool, storage, partitioning, or clustering choices your database standards prefer. Always inspect the resulting catalog entries.

Consider an existing unique index

A suitable existing unique index may be designated to enforce the primary key. Before relying on one, compare its table, key columns, column order, uniqueness, expressions, and other relevant attributes with the planned constraint. An index on (ORDER_ID, LINE_NO) is not interchangeable with one on (LINE_NO, ORDER_ID) for ordered access, even though both can make the pair unique. An index that includes extra key columns does not prove that the shorter primary-key combination is unique.

Some teams deliberately create the exact unique index first so they control its physical design and discover duplicates during the index build before the constraint change. This can be valuable, but it does not replace the constraint: applications and catalog tools should see the declared entity rule, not infer it from an index. Confirm in a test environment whether DB2 selects the intended index and how it marks that index after the primary key is added.

Verify the constraint in the DB2 catalog

A successful completion code is only the first check. Query SYSTABCONST to confirm the table constraint, SYSKEYCOLUSE to confirm every key column and its sequence, and SYSINDEXES to identify the unique primary index. Catalog column availability can vary by DB2 release, so adapt selected diagnostic columns to your supported catalog level.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT CREATOR, NAME, CONSTNAME, TYPE
FROM SYSIBM.SYSTABCONST
WHERE CREATOR = 'APP'
  AND NAME = 'ORDER_LINE'
  AND TYPE = 'P';
 
SELECT TBCREATOR, TBNAME, CONSTNAME, COLNAME, COLSEQ
FROM SYSIBM.SYSKEYCOLUSE
WHERE TBCREATOR = 'APP'
  AND TBNAME = 'ORDER_LINE'
  AND CONSTNAME = 'PK_ORDER_LINE'
ORDER BY COLSEQ;
 
SELECT CREATOR, NAME, TBCREATOR, TBNAME, UNIQUERULE, CLUSTERING
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'APP'
  AND TBNAME = 'ORDER_LINE'
ORDER BY CREATOR, NAME;

For the enforcing primary index, review the unique-rule designation, key metadata, and operational attributes rather than choosing the first index returned. Then run negative tests in a controlled environment: attempt a duplicate key and a null key, confirm that DB2 rejects them, roll back the test unit of work, and run a valid insert or update. Finally, test child-table foreign keys and representative application transactions.

Loading and utility impact

Adding a primary key to a populated table can be expensive because DB2 must establish that all existing rows satisfy uniqueness and build or designate an index. A large index build consumes CPU, elapsed time, sort space, I/O, and log or utility resources. DDL and utility serialization can also conflict with applications, LOAD, REORG, COPY, or other activity. Estimate the work from production-like volume instead of extrapolating from an empty test table.

Review every LOAD process. Future input must provide non-null key values and must not duplicate existing or incoming rows. Utility options determine when constraints and indexes are enforced, what rows are discarded, and whether an object enters a restrictive state that requires CHECK DATA, REBUILD INDEX, REORG, or another documented action. Do not assume that an old LOAD card remains safe after the new constraint.

• Schedule an approved maintenance window when validation or index creation can block work.
• Check table-space and index-space status before and after every DDL and utility step.
• Reserve adequate sort, work-file, storage, and elapsed-time capacity for the index build.
• Update LOAD, application, replication, and recovery procedures with the new key rule.
• Run RUNSTATS after the index is established so the optimizer has representative statistics.
• Take the required image copy or recovery checkpoint under local standards.

Common errors and what they mean

Exact SQLCODEs depend on the failed operation and object state. Capture the complete SQLCA, DSNTIAR text, and utility messages instead of reducing an incident to a generic "ALTER failed." The constraint name, conflicting key, index name, and reason code usually point to the correct remediation. If production data ownership is unclear, stop and involve the application owner rather than making irreversible cleanup guesses.

A safe deployment sequence

1. Define the business entity and candidate key. Confirm ownership, retention rules, composite column order, dependent applications, and future foreign-key use.
2. Inventory the current table definition, constraints, indexes, dependent views, packages, LOAD jobs, replication processes, and utility schedules.
3. Run null and full-key duplicate queries against a stable data point. Reconcile counts and resolve defects through an approved business rule.
4. Test NOT NULL alterations, unique index strategy, primary-key DDL, elapsed time, object states, and rollback or forward-recovery steps on production-like data.
5. Change applications and inbound files so every new row supplies a complete unique key. Deploy those prerequisites before database enforcement when sequencing requires it.
6. In the maintenance window, stop conflicting work, rerun the inspection queries, capture baseline catalog and object-status evidence, and make the key columns NOT NULL.
7. Create the planned unique index when your design calls for one, then add the named PRIMARY KEY constraint with the exact approved column order.
8. Verify SYSTABCONST, SYSKEYCOLUSE, SYSINDEXES, object status, duplicate rejection, valid transactions, utilities, and dependent foreign-key behavior.
9. Run required RUNSTATS, REORG, COPY, or validation work, monitor performance and errors, save evidence, and release the change only when acceptance checks pass.

A backout plan must reflect reality. After new application data relies on the constraint or child tables reference it, simply dropping the primary key may be unsafe or blocked by dependencies. Prefer a rehearsed forward-fix where possible, and document the point after which recovery requires coordinated application and database action.

Explain it like I'm 5

Imagine a classroom where every child receives one badge number. No badge can be blank, and two children cannot have the same number. The teacher's rule saying "badge number identifies the child" is the primary key. The sorted badge list the teacher checks quickly is the unique index. For children on teams, the badge might use both team number and child number; that is a composite key. Before making the rule official, the teacher checks that nobody has a blank or copied badge.

Exercises

1. Write null and duplicate inspection SQL for APP.ENROLLMENT with a proposed composite key of STUDENT_ID, COURSE_ID, and TERM_CODE. Explain why three separate duplicate queries would give the wrong answer.
2. Compare PRIMARY KEY (ORDER_ID, LINE_NO) with PRIMARY KEY (LINE_NO, ORDER_ID). State what remains logically equivalent and what changes physically for likely access paths.
3. A table already has a unique index on (ACCOUNT_ID, REGION_CODE, STATUS). Decide whether it proves that (ACCOUNT_ID, REGION_CODE) is unique, and justify your answer.
4. Build a catalog verification checklist using SYSTABCONST, SYSKEYCOLUSE, SYSINDEXES, and object-status checks. Include the expected constraint type and column sequence.
5. Draft a production runbook for a 500-million-row table. Include application quiescing, sort and storage estimates, LOAD changes, RUNSTATS, recovery evidence, acceptance tests, and the decision point for backout versus forward-fix.