How to Use Primary Key in Oracle

Uniqueness applies to the combination: (1, 1) and (1, 2) can both exist. A primary key can have at most 32 columns, and its columns cannot be LOB, LONG, LONG RAW, BFILE, REF, TIMESTAMP WITH TIME ZONE, object or collection types.

Column order matters for performance. The supporting index is on (order_id, line_no), so it serves queries that filter on order_id but is a poor fit for queries that filter only on line_no.

Generating key values

Use an identity column, available since Oracle 12c, instead of a hand-built sequence and trigger:

CREATE TABLE orders (
  order_id    NUMBER GENERATED ALWAYS AS IDENTITY
              CONSTRAINT pk_orders PRIMARY KEY,
  customer_id NUMBER(10) NOT NULL
              CONSTRAINT fk_orders_customers REFERENCES customers (customer_id),
  order_date  DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Acme Ltd');
INSERT INTO orders (customer_id) VALUES (1);

SELECT order_id, customer_id FROM orders;

  ORDER_ID CUSTOMER_ID
---------- -----------
         1           1

GENERATED ALWAYS rejects an explicit value for the column. GENERATED BY DEFAULT ON NULL allows you to supply one, which is useful when loading existing data. Identity values can have gaps after rollbacks or restarts; treat them as unique, not consecutive.

Find the primary key of a table

SELECT c.constraint_name, c.status, c.validated, c.index_name,
       cc.column_name, cc.position
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.table_name = 'ORDER_ITEMS'
AND    c.constraint_type = 'P'
ORDER  BY cc.position;

CONSTRAINT_NAME  STATUS   VALIDATED  INDEX_NAME      COLUMN_NAME  POSITION
---------------- -------- ---------- --------------- ------------ --------
PK_ORDER_ITEMS   ENABLED  VALIDATED  PK_ORDER_ITEMS  ORDER_ID            1
PK_ORDER_ITEMS   ENABLED  VALIDATED  PK_ORDER_ITEMS  LINE_NO             2

Table names are stored in upper case unless they were created with quotes. Use the ALL_ or DBA_ versions of these views, with an owner filter, for tables in other schemas.

Add a primary key to a table that has data

Suppose order_items had been created without its key and already holds rows:

ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_no);

Oracle validates every existing row. If there are duplicates or nulls the statement fails with ORA-02437: cannot validate (SCHEMA.PK_ORDER_ITEMS) - primary key violated. Find the offenders first:

SELECT order_id, line_no, COUNT(*)
FROM   order_items
GROUP  BY order_id, line_no
HAVING COUNT(*) > 1;

SELECT COUNT(*) FROM order_items WHERE order_id IS NULL OR line_no IS NULL;

On a large, busy table, build the index online first and then attach the constraint to it, so the table is not locked for the duration of the index build:

CREATE UNIQUE INDEX pk_order_items ON order_items (order_id, line_no) ONLINE;

ALTER TABLE order_items ADD CONSTRAINT pk_order_items
  PRIMARY KEY (order_id, line_no) USING INDEX pk_order_items;

Drop a primary key

ALTER TABLE order_items DROP CONSTRAINT pk_order_items;
-- or, without needing the name:
ALTER TABLE order_items DROP PRIMARY KEY;

Two things to know:

• The index. If Oracle created the index for the constraint, dropping the constraint drops the index too. If the constraint was attached to an index that already existed, that index is kept. Say what you want explicitly with DROP PRIMARY KEY KEEP INDEX or DROP INDEX.
• Foreign keys. If other tables reference the key you get ORA-02273: this unique/primary key is referenced by some foreign keys. ALTER TABLE customers DROP PRIMARY KEY CASCADE drops those foreign keys as well. They are not recreated when you add the primary key back.

Disable and enable

Disabling keeps the constraint definition but stops enforcing it, which is sometimes done for bulk loads:

ALTER TABLE order_items DISABLE CONSTRAINT pk_order_items;

-- load data

ALTER TABLE order_items ENABLE CONSTRAINT pk_order_items;

Disabling normally drops the unique index, and enabling rebuilds it and rechecks every row, so on a big table this is expensive. Add KEEP INDEX when disabling if you want the index to survive. While the constraint is disabled, nothing stops duplicates, and ENABLE fails with ORA-02437 if any were loaded.

ENABLE NOVALIDATE enforces the rule for new rows without checking existing ones. It only works if the supporting index is non-unique, because a unique index cannot be built over duplicates.

A primary key that foreign keys depend on cannot be disabled on its own (ORA-02297). Disable the foreign keys first, or use DISABLE PRIMARY KEY CASCADE, and remember to re-enable each foreign key afterwards.

Common errors

Good practice

• DDL commits implicitly. CREATE TABLE and ALTER TABLE need no COMMIT, and they commit any open transaction in your session.
• Prefer a surrogate key that never changes. Updating a primary key value that child rows reference fails, and Oracle has no ON UPDATE CASCADE.
• Primary key or unique constraint? A unique constraint allows nulls and a table can have many. Use the primary key for the row's identity and unique constraints for natural keys such as email.
• Index the foreign key columns in child tables. Oracle indexes the primary key automatically but not the columns that reference it. Unindexed foreign keys cause full scans and table-level locking when parent rows are deleted or their keys updated.
• Leave keys enabled in production. A disabled key enforces nothing, and the optimizer can no longer rely on the column being unique.

Further reading

• SQL Language Reference: constraint
• SQL Language Reference: ALTER TABLE
• Database Concepts

What is New in Oracle’s Eighth Generation SPARC Systems

In this article, we will see what the newly launched Eighth Generation of Oracle SPARC platform has to offer.