FOREIGN KEY, CHECK, and UNIQUE Constraints in Oracle 26ai

Create the referenced keys first

A foreign key refers to a key whose uniqueness Oracle can guarantee. The parent constraint must already exist and be enabled before Oracle can enable the child foreign key. The following parent table supplies both kinds of eligible referenced keys:

CREATE TABLE product (
    product_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    product_code VARCHAR2(30)  NOT NULL,
    product_name VARCHAR2(100) NOT NULL,
    CONSTRAINT product_pk PRIMARY KEY (product_id),
    CONSTRAINT product_code_uk UNIQUE (product_code)
);

PRODUCT_ID is the chosen primary key. PRODUCT_CODE is a mandatory alternate key because it has both NOT NULL and UNIQUE constraints. Either key can be referenced explicitly. If a REFERENCES product clause omits its column list, Oracle uses the primary key in its defined order; to target PRODUCT_CODE, the child definition must name that column.

A unique key is not a second primary key, and a unique constraint is not merely a performance index. It declares a candidate-key rule. Oracle uses a suitable index to enforce that rule, while the constraint remains the database object that records its meaning and enforcement state.

Define an out-of-line FOREIGN KEY

The table containing the foreign key is the child table; the table containing the referenced key is the parent. Parent and child can also be the same table in a self-referencing design. An out-of-line constraint appears in the table-level list and names both the child columns and the referenced columns.

CREATE TABLE pet_care_log (
    log_id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
    product_id      NUMBER,
    log_datetime    TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    log_type        VARCHAR2(20),
    log_text        VARCHAR2(500),
    update_datetime TIMESTAMP,
    CONSTRAINT pet_care_log_pk PRIMARY KEY (log_id),
    CONSTRAINT pet_care_product_fk
        FOREIGN KEY (product_id)
        REFERENCES product (product_id)
        ON DELETE SET NULL,
    CONSTRAINT pet_care_log_type_ck
        CHECK (log_type IN ('GOOD', 'BAD', 'UGLY')) PRECHECK,
    CONSTRAINT pet_care_log_dates_ck
        CHECK (update_datetime IS NULL
               OR update_datetime >= log_datetime)
);

PET_CARE_PRODUCT_FK requires each non-null PRODUCT_ID in the log to exist in PRODUCT. Corresponding child and parent columns must match in number, order, data type, and declared collation requirements. The relationship is enforced through matching values; the foreign key is not a physical pointer stored in the row.

The child PRODUCT_ID is intentionally nullable because the selected action is ON DELETE SET NULL. Deleting a product keeps its log rows but removes their relationship by setting that column to null. If PRODUCT_ID were also declared NOT NULL, the requested null assignment could not succeed and the parent delete would fail.

The check using SYSTIMESTAMP does not place that nondeterministic function in a check condition. It appears only as the default for LOG_DATETIME. The two check conditions compare stored row values, which is the appropriate scope for an ordinary table check.

Use inline syntax for a single-column foreign key

A single-column foreign key can be declared inside its column specification. In this form, the optional constraint name precedes REFERENCES, and the words FOREIGN KEY are omitted. Contrary to older descriptions of Oracle syntax, an inline foreign key can have an explicit user-supplied name.

CREATE TABLE pet_care_note (
    note_id NUMBER GENERATED BY DEFAULT AS IDENTITY
        CONSTRAINT pet_care_note_pk PRIMARY KEY,
    product_id NUMBER
        CONSTRAINT pet_care_note_product_fk
        REFERENCES product (product_id)
        ON DELETE CASCADE,
    note_text VARCHAR2(500) NOT NULL
);

Here, deleting a product also deletes its dependent notes. That rule is appropriate only if a note is wholly owned by its product and has no independent retention requirement. Inline syntax is concise for one column; a composite foreign key must be declared out of line.

Match the columns of a composite foreign key

Lesson 5 defined GROCERY_STORE with the composite primary key (TERRITORY, STORE_NUMBER). Assuming that STORE_DELIVERY and its corresponding columns already exist, the relationship can be added as follows:

ALTER TABLE store_delivery
ADD CONSTRAINT store_delivery_store_fk
    FOREIGN KEY (territory, store_number)
    REFERENCES grocery_store (territory, store_number);

Child and parent lists must contain the same number of compatible columns in corresponding order. A composite child key satisfies the constraint when it matches the referenced composite key or when at least one child-key column is null. If partial keys would violate the business rule, add NOT NULL constraints to every child-key component.

Because this statement omits ON DELETE, Oracle prevents deletion of a referenced store while dependent delivery rows exist. The data dictionary reports this default rule as NO ACTION. Oracle's references_clause does not provide an ON UPDATE action, so referenced keys should be stable and any key-value change must be planned explicitly.

Use a self-referencing foreign key for a hierarchy

Parent and child roles describe the two sides of a relationship; they do not require two different tables. A self-referencing foreign key lets one row refer to another row in the same table. The following employee hierarchy uses MANAGER_ID to refer back to the table's primary key:

CREATE TABLE employee (
    employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    manager_id  NUMBER,
    full_name   VARCHAR2(100) NOT NULL,
    CONSTRAINT employee_pk PRIMARY KEY (employee_id),
    CONSTRAINT employee_manager_fk
        FOREIGN KEY (manager_id)
        REFERENCES employee (employee_id)
        ON DELETE SET NULL
);

A top-level employee can have a null MANAGER_ID. Every non-null manager value must identify another employee row. If a manager row is deleted, SET NULL retains the direct reports and makes them temporarily top-level. A different business process might omit the delete action and require reports to be reassigned before a manager can be removed.

The foreign key guarantees that the referenced manager exists; it does not prove that the resulting graph is acyclic or that an employee is not their own manager. A simple same-row check can prohibit direct self-reference only when both values are available, but preventing longer cycles requires a broader business rule and transaction-aware design. This distinction illustrates why choosing the right declarative mechanism matters: referential integrity enforces existence, while topology rules go beyond an ordinary foreign key.

Choose parent-delete behavior deliberately

None of these actions is universally best. A sales line might need to block deletion of its product for auditability. A temporary child record might appropriately disappear with its parent. A historical log might survive but lose the active relationship. The database rule should match the actual ownership, retention, and legal requirements of the data.

Index child foreign keys according to the workload

Oracle does not automatically create an index on child foreign-key columns merely because the constraint exists. The referenced primary or unique key has an enforcement index, but that parent index is separate from any child-table access path.

A child-key index is often valuable for joins and for finding dependent rows when a referenced key is deleted or changed. It can also reduce locking and scanning work during parent-key operations. However, every index consumes storage and adds maintenance to child-table DML. Review table size, join patterns, parent deletion and key-change frequency, and the leading columns of existing indexes before adding another one.

Understand CHECK constraint truth values

A check constraint evaluates its condition for each affected row. SQL uses three-valued logic, so the condition can be true, false, or unknown. Only false violates the check.

Consequently, CHECK (amount > 0) rejects zero and negative values but permits a null amount because the comparison becomes unknown. Add NOT NULL when a value must be present. The LOG_TYPE column in the earlier table is nullable, so its check limits supplied values to three choices while still permitting no value.

Use inline and out-of-line CHECK constraints

An inline check can refer only to the column currently being defined. An out-of-line check can refer to several columns in the same row. In the PET_CARE_LOG definition, PET_CARE_LOG_TYPE_CK restricts one column to a controlled set. The out-of-line PET_CARE_LOG_DATES_CK compares two stored timestamps while allowing an update timestamp to remain absent.

A check condition is more restricted than a query WHERE clause. It cannot contain a subquery or scalar subquery, refer to another table, call a user-defined function, or use Oracle's documented nondeterministic functions such as SYSDATE, SYSTIMESTAMP, CURRENT_DATE, USER, or USERENV. It also cannot use the pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.

Ordinary table checks therefore enforce conditions within one row of one table. Oracle does not verify that multiple check constraints are mutually consistent, and it does not promise an evaluation order. Prefer several clearly named checks that each express one rule, but review their combined logic so they do not contradict one another.

Review UNIQUE syntax in this relationship

A single-column unique constraint can be inline or out of line. A composite unique key must be declared out of line. A table can have several unique constraints, and each one can protect an alternate candidate key. Nulls remain permitted under Oracle's documented unique-key semantics, so add NOT NULL when every row must supply the business identifier.

The earlier PRODUCT_CODE_UK constraint is especially relevant here because a child table can reference it directly:

ALTER TABLE product_alias
ADD CONSTRAINT product_alias_code_fk
    FOREIGN KEY (product_code)
    REFERENCES product (product_code);

This statement assumes that PRODUCT_ALIAS and its PRODUCT_CODE column already exist. Naming the referenced columns makes clear that the relationship targets the alternate unique key rather than the parent's primary key.

Current 26ai extensions to declarative integrity

Oracle AI Database 26ai can mark eligible check constraints as PRECHECK when an equivalent JSON Schema representation exists. This allows applications to validate prospective data before sending it to the database. The earlier PET_CARE_LOG_TYPE_CK uses this state. Oracle raises an error if a requested precheck condition has no equivalent JSON Schema rather than silently claiming compatibility.

Prechecking improves early feedback but does not replace database enforcement. Other applications, scripts, and concurrent sessions can still write to the table. The enabled database constraint remains the authoritative rule. Oracle exposes the precheck state through USER_CONSTRAINTS.PRECHECK, and DBMS_JSON_SCHEMA.DESCRIBE can expose schemas for compatible constraints.

ALTER TABLE pet_care_log
MODIFY CONSTRAINT pet_care_log_type_ck PRECHECK;

Oracle 26ai also introduces schema-level SQL assertions for declarative rules involving multiple rows or tables. Assertions address a boundary of ordinary checks, which cannot query another row or table. They require their own privileges and appear through assertion metadata such as USER_ASSERTIONS. Use a foreign key, unique constraint, or same-row check when it already expresses the rule; use the broader mechanism only when the business rule genuinely crosses those boundaries.

Add constraints to populated tables carefully

An ordinary new constraint is enabled and validated by default. Adding a foreign key fails validation when an existing non-null child key has no matching parent. Adding a check fails when an existing row makes its condition false. Adding a unique constraint fails when existing non-null values or combinations violate uniqueness.

The normal migration sequence is to profile the existing rows, correct violations, add the constraint, and verify its state. The specialized ENABLE NOVALIDATE state enforces subsequent DML without proving that all preexisting data conforms. It can support controlled migrations and warehouse operations, but it must not be described as equivalent to an enabled, validated application constraint.

Verify constraints in the data dictionary

USER_CONSTRAINTS reports definitions and states for the current user's tables. USER_CONS_COLUMNS reports participating columns and their order. The self-join below follows a foreign key's R_CONSTRAINT_NAME to the referenced primary or unique constraint:

SELECT c.constraint_name,
       c.constraint_type,
       c.status,
       c.validated,
       c.delete_rule,
       c.precheck,
       p.table_name AS referenced_table,
       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
LEFT JOIN user_constraints p
       ON p.constraint_name = c.r_constraint_name
WHERE  c.table_name = 'PET_CARE_LOG'
AND    c.constraint_type IN ('R', 'C', 'U')
ORDER  BY c.constraint_name, cc.position;

Type R identifies referential integrity, C identifies check metadata and also represents NOT NULL constraints, and U identifies a unique constraint. DELETE_RULE reports CASCADE, SET NULL, or NO ACTION for referential constraints. PRECHECK reports whether a compatible check is precheckable.

Read STATUS and VALIDATED together: enabled reports current enforcement, while validated reports whether existing data was checked. Use corresponding ALL_ or DBA_ views only when broader visibility is required and authorized. In a multitenant database, interpret results in the current container.

Constraint design checklist

1. Define the parent primary or unique key before enabling a foreign key.
2. Match child and parent columns in number, order, type, and collation requirements.
3. Choose default deletion, CASCADE, or SET NULL from the real data lifecycle.
4. Decide whether nullable child keys and partially null composite keys are valid.
5. Use NOT NULL with a check or unique constraint when absence must fail.
6. Keep each check within one row and avoid prohibited expressions.
7. Index child foreign-key columns when workload evidence and relationship operations justify it.
8. Repair existing data, then verify constraint type, state, columns, delete rule, and precheck status through the dictionary.

Foreign keys protect relationships, check constraints protect same-row conditions, and unique constraints protect alternate candidate keys. Their declarative definitions place business rules in inspectable database metadata and apply those rules to every authorized write path. The next lesson moves from creating constraints to modifying existing column definitions safely.

FOREIGN KEY, CHECK, and UNIQUE Constraints - Exercise