PRIMARY KEY and UNIQUE Constraints in Oracle 26ai

Advantages of declaring a primary key

Oracle does not require every ordinary heap table to declare a primary key, but most application tables should have one. The important benefits come from an enforced and discoverable row identity, not from treating the clause as a universal performance switch.

1. Reliable row identity: duplicate identifiers and null identifiers are rejected. Applications, administrators, and integration processes can refer to one row without depending on its physical location or current display order.
2. Database-wide integrity: the rule is enforced for every authorized client. It is not limited to validation implemented by one form, service, or batch program.
3. A referenced candidate key: foreign keys in child tables can reference the primary key, allowing Oracle to enforce relationships between rows. An eligible unique key can also be referenced, as shown later in this lesson.
4. Explicit schema metadata: tools and developers can discover the key through Oracle's data dictionary. A named constraint also produces more understandable diagnostics and deployment scripts than an unnamed, undocumented application assumption.
5. Efficient key access: Oracle uses an index to enforce an enabled primary key. That index often provides an efficient access path for equality searches and joins on the key, although the optimizer still chooses the plan for each statement.
6. Stable schema evolution: a declared key gives foreign keys, object-relational mappings, APIs, imports, and migration tools an explicit identifier to preserve. Stability is a design benefit; it does not eliminate the need to plan key changes carefully.

These benefits have boundaries. A primary key does not define a column's data type or formatting rules, normalize a poorly designed schema, implement row-level security, or automatically partition or cluster a table. Its supporting index also has a cost: inserts, deletes, and updates of key values must maintain that index. The correct claim is that the key provides integrity and commonly useful indexed access, not that every data manipulation operation becomes faster.

Candidate, primary, and alternate keys

Choose stable and minimal keys

CREATE TABLE customer_account (
    customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY
        CONSTRAINT customer_account_pk PRIMARY KEY,
    email_address        VARCHAR2(254) NOT NULL,
    external_customer_id VARCHAR2(30)  NOT NULL,
    display_name         VARCHAR2(100) NOT NULL,
    CONSTRAINT customer_account_email_uk
        UNIQUE (email_address),
    CONSTRAINT customer_account_external_uk
        UNIQUE (external_customer_id)
);

CUSTOMER_ID supplies the chosen row identity. The two unique constraints separately prevent duplicate email addresses and duplicate external identifiers. Neither alternate key is mislabeled as a second primary key. If the business later permits a missing email address, its NOT NULL rule can be reconsidered independently from the primary key.

Key equality also follows the data representation and declared collation. If the business considers differently cased, padded, or formatted text to represent the same identifier, model that equivalence deliberately. Do not assume that a basic unique constraint will normalize values before comparing them. Choose an appropriate data type and collation, standardize input where required, or use a separately designed virtual column or function-based unique index for conditional or normalized uniqueness.

PRIMARY KEY compared with UNIQUE

The SQL clause is UNIQUE; the phrase unique key describes the protected column or column combination. A unique key is not a "second primary key." It normally represents a different candidate key, and its treatment of nulls is intentionally different.

Define a single-column primary key inline

An inline constraint appears inside one column definition. This form is concise when exactly one column forms the key. Naming the constraint is optional syntactically, but an explicit name is preferable in maintainable schemas. Without one, Oracle generates a name such as SYS_Cn.

CREATE TABLE grocery_bag (
    bag_number    NUMBER(10)
        CONSTRAINT grocery_bag_pk PRIMARY KEY,
    material_code VARCHAR2(10)
);

The declaration makes BAG_NUMBER both unique and non-null. A separate NOT NULL clause is unnecessary for this column. MATERIAL_CODE remains nullable because its definition contains no non-null constraint. The primary key does not validate the format or meaning of either value; data types and additional constraints handle those separate concerns.

Use an out-of-line constraint for a composite key

An out-of-line constraint appears in the table-level list and explicitly names its participating columns. A composite primary or unique key must use this form. Column order is part of the key definition and is reported through the data dictionary.

CREATE TABLE grocery_store (
    territory      VARCHAR2(10),
    store_number   NUMBER(5),
    store_name     VARCHAR2(100) NOT NULL,
    tax_identifier VARCHAR2(30),
    CONSTRAINT grocery_store_pk
        PRIMARY KEY (territory, store_number),
    CONSTRAINT grocery_store_tax_id_uk
        UNIQUE (tax_identifier)
);

The ordered pair (TERRITORY, STORE_NUMBER) identifies each store. Oracle prevents duplicate pairs and makes both participating columns non-null. The table can repeat a territory and can repeat a store number in different territories; it cannot repeat the complete pair.

TAX_IDENTIFIER is an alternate key protected by GROCERY_STORE_TAX_ID_UK. It remains nullable in this definition. If the business rule requires every store to supply a tax identifier, declare that column NOT NULL as well as UNIQUE. Uniqueness and mandatory entry are separate decisions for an alternate key.

Understand nulls in a UNIQUE constraint

Add a primary key with ALTER TABLE

A key can be added after table creation. The following statement assumes that GROCERY_STORE_STAGE already contains the named columns and does not already have a primary key:

ALTER TABLE grocery_store_stage
ADD CONSTRAINT grocery_store_stage_pk
    PRIMARY KEY (territory, store_number);

Oracle enables and validates an ordinary newly added constraint by default. If existing rows contain a duplicate (TERRITORY, STORE_NUMBER) combination, or either key component is null, the statement fails and the primary key is not enabled. The normal response is to profile the table, correct or remove nonconforming rows, and then add the constraint.

ENABLE NOVALIDATE is available for controlled migration or data-warehouse workflows. It enforces the rule for subsequent DML without guaranteeing that every preexisting row conforms. That state should not be presented as equivalent to a validated application key. DISABLE removes enforcement, although the constraint definition remains visible in the dictionary.

Constraint deferral solves a different problem. The default is nondeferrable immediate checking. A deliberately declared DEFERRABLE constraint can be initially immediate or initially deferred until transaction end. Deferral can support specialized multi-statement transactions, but it is not a substitute for repairing invalid data, and it is separate from validation state.

A foreign key can reference a UNIQUE key

Oracle foreign keys can reference an enabled primary key or an eligible unique key. This allows a relationship to use a stable alternate business identifier when the model calls for it. Because the earlier GROCERY_STORE definition makes TAX_IDENTIFIER unique, another table can reference that column explicitly:

CREATE TABLE store_tax_profile (
    tax_identifier VARCHAR2(30)
        CONSTRAINT store_tax_profile_pk PRIMARY KEY,
    filing_region  VARCHAR2(30),
    CONSTRAINT store_tax_profile_store_fk
        FOREIGN KEY (tax_identifier)
        REFERENCES grocery_store (tax_identifier)
);

The child and referenced keys must have corresponding columns in the same order with compatible data types and declared collations. When a REFERENCES parent_table clause omits the parent columns, Oracle targets the parent's primary key. To reference an alternate unique key, name its columns as the example does. The next lesson develops foreign-key behavior and delete actions in greater detail.

Separate the integrity rule from its index

When a suitable index already exists, Oracle can use it to enforce the key. Depending on the definition, a usable existing index can be unique or nonunique. If no suitable index exists, Oracle creates one; in the ordinary immediate default case this is a unique index. The optional USING INDEX clause can control the enforcement index when physical design requirements justify doing so.

Avoid creating a redundant index on the same columns merely because a constraint exists. Also avoid assuming that disabling or dropping the constraint always produces one index outcome. Oracle can retain a user-supplied index, while an index created to support the constraint can be dropped according to the operation and options such as KEEP INDEX. The constraint and index have related but distinct lifecycles.

Verify key constraints in the data dictionary

USER_CONSTRAINTS describes constraints on tables owned by the current user. USER_CONS_COLUMNS identifies their columns and preserves each column's position. The following query combines both views for the primary and unique constraints on GROCERY_STORE:

SELECT c.constraint_name,
       c.constraint_type,
       c.status,
       c.validated,
       c.deferrable,
       c.deferred,
       c.index_name,
       cc.position,
       cc.column_name
FROM   user_constraints c
JOIN   user_cons_columns cc
       ON cc.constraint_name = c.constraint_name
      AND cc.table_name = c.table_name
WHERE  c.table_name = 'GROCERY_STORE'
AND    c.constraint_type IN ('P', 'U')
ORDER  BY c.constraint_name, cc.position;

A constraint type of P identifies a primary key and U identifies a unique constraint. STATUS reports whether enforcement is enabled. VALIDATED reports whether existing data has been validated. DEFERRABLE and DEFERRED describe checking behavior, while INDEX_NAME identifies the enforcement index when Oracle reports one. POSITION reveals the defined order of columns in a composite key.

Use the corresponding ALL_ or DBA_ views only when broader object visibility is required and the user has the necessary privileges. In a multitenant database, interpret results in the current container. The same schema or table name in another pluggable database describes a different object and constraint set.

Practical key-design checklist

1. Identify minimal candidate keys from stable business rules.
2. Choose one candidate key as the table's primary key.
3. Protect other required candidate keys with named UNIQUE constraints.
4. Decide explicitly whether every alternate-key column must be non-null.
5. Use an inline declaration for a single column or an out-of-line declaration for a composite key.
6. Profile and repair existing data before adding an enabled, validated key.
7. Verify the key columns, order, enforcement state, validation state, and index through the data dictionary.
8. Avoid redundant indexes and avoid treating a key as a substitute for security, normalization, or physical-design work.

A carefully chosen primary key gives each row a dependable identity, and unique constraints preserve other candidate keys without confusing them with that primary identity. Together they move business rules out of application assumptions and into enforced, inspectable database metadata. The next lesson extends this foundation with foreign key, check, and other constraints used to protect relationships and valid values.