11.2 Integrity Constraints

11.2 Integrity Constraints

Integrity constraints are declarative rules defined at the schema level to ensure business rules are enforced automatically by the database engine. By declaring constraints directly in the data dictionary, Oracle guarantees data consistency across all client applications and prevents invalid DML operations.

For the 1Z0-071 exam, you must understand the five constraint types, the architectural differences between inline and out-of-line syntax, the exact rules governing CHECK constraints, and the mechanics of referential integrity delete actions.

---

The Five Oracle Integrity Constraint Types

                         ORACLE INTEGRITY CONSTRAINTS
                                      |
      +---------------+---------------+---------------+---------------+
      |               |               |               |               |
  PRIMARY KEY    FOREIGN KEY       UNIQUE          NOT NULL         CHECK
  - Unique +      - Referential    - Unique values - No NULLs       - Boolean
    NOT NULL        integrity      - Allows NULLs  - Inline only      condition
  - Unique index  - Matches PK/UK  - Unique index                    - Strict rules

Constraint Taxonomy and Rules Matrix

---

Inline vs. Out-of-Line Constraint Syntax

Oracle allows constraints to be declared in two syntactical positions:

1. Inline (Column-Level): Defined immediately following the column datatype definition, before the comma. Suitable for single-column constraints.
2. Out-of-Line (Table-Level): Defined at the end of the CREATE TABLE statement after all column definitions, separated by commas. Required for composite constraints.

+-------------------------------------------------------------------------+
|                 INLINE VS. OUT-OF-LINE SYNTAX RULES                     |
+-------------------------------------------------------------------------+
| Constraint Type     | Inline (Column-Level)? | Out-of-Line (Table-Level)?|
| :------------------ | :--------------------- | :------------------------ |
| NOT NULL            | YES (Mandatory)        | NO (Cannot be declared)   |
| Single-Column PK    | YES                    | YES                       |
| Composite PK (2+ col)| NO                     | YES (Mandatory)           |
| Single-Column FK    | YES (Omits 'FOREIGN KEY')| YES (Includes 'FOREIGN KEY')|
| Composite FK (2+ col)| NO                     | YES (Mandatory)           |
| Single-Column UNIQUE| YES                    | YES                       |
| Composite UNIQUE    | NO                     | YES (Mandatory)           |
| CHECK Constraint    | YES                    | YES                       |
+-------------------------------------------------------------------------+

Syntax Comparison Example

-- COMPREHENSIVE DDL DEMONSTRATING BOTH SYNTAXES
CREATE TABLE order_items (
    -- 1. Inline NOT NULL constraint:
    item_id       NUMBER(10)      CONSTRAINT item_id_nn NOT NULL,
    order_id      NUMBER(10)      CONSTRAINT item_order_id_nn NOT NULL,
    product_id    NUMBER(6)       NOT NULL,
    -- 2. Inline CHECK constraint:
    unit_price    NUMBER(8, 2)    CONSTRAINT item_price_chk CHECK (unit_price > 0),
    quantity      NUMBER(4)       DEFAULT 1 NOT NULL,
    
    -- 3. Out-of-line Composite PRIMARY KEY (Mandatory out-of-line for 2+ columns):
    CONSTRAINT order_items_pk PRIMARY KEY (item_id, order_id),
    
    -- 4. Out-of-line FOREIGN KEY referencing ORDERS table:
    CONSTRAINT order_items_order_fk FOREIGN KEY (order_id)
        REFERENCES orders (order_id) ON DELETE CASCADE,
        
    -- 5. Out-of-line Composite CHECK constraint:
    CONSTRAINT item_qty_chk CHECK (quantity >= 1 AND quantity <= 1000)
);

Key Syntactical Difference in Foreign Keys:

• Inline: dept_id NUMBER(4) REFERENCES departments(dept_id) (The keywords FOREIGN KEY are omitted).
• Out-of-Line: CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments(dept_id) (The keywords FOREIGN KEY (...) are required).

---

CHECK Clause Prohibitions & Strict Exam Rules

The CHECK constraint evaluates an arbitrary boolean expression on the row being inserted or updated. If the condition evaluates to TRUE or UNKNOWN (NULL), Oracle accepts the row. If the expression evaluates to FALSE, Oracle raises ORA-02290: check constraint violated.

-- Valid CHECK constraints:
CHECK (salary > 0)
CHECK (commission_pct >= 0 AND commission_pct <= 0.50)
CHECK (status IN ('OPEN', 'PENDING', 'CLOSED', 'CANCELLED'))
CHECK (end_date >= start_date)

Prohibited Elements in CHECK Constraints (1Z0-071 Tested!)

Oracle strictly disallows the following constructs inside a CHECK constraint condition:

1. Subqueries and Scalar Queries: CHECK (dept_id IN (SELECT id FROM depts)) $\rightarrow$ ORA-02251.
2. Non-Deterministic Date Functions: CHECK (hire_date <= SYSDATE) or CHECK (created_at >= CURRENT_DATE) $\rightarrow$ ORA-02436.
3. Sequence Pseudocolumns: CHECK (order_id > order_seq.CURRVAL) or NEXTVAL $\rightarrow$ ORA-02287.
4. Environment / Session Functions: CHECK (created_by = USER) or UID, USERENV, SYS_CONTEXT $\rightarrow$ ORA-02436.
5. Pseudocolumns: ROWNUM, LEVEL, and PRIOR $\rightarrow$ ORA-00976: specified pseudocolumn or operator not allowed here. Oracle's documented prohibition list for CHECK conditions is CURRVAL, NEXTVAL, LEVEL, and ROWNUM.
6. User-Defined PL/SQL Functions: Custom stored functions cannot be referenced in a CHECK condition.
7. Cross-Table Column References: A CHECK constraint can only reference columns belonging to the same row of the current table.

---

Referential Delete Actions

When a foreign key is established, it binds child rows to parent rows. If a user attempts to delete a parent row referenced by existing child rows, Oracle's default behavior is to reject the deletion with ORA-02292: integrity constraint violated - child record found.

You can override this default behavior using referential delete actions:

                    PARENT ROW DELETION BEHAVIOR
                                 |
         +-----------------------+-----------------------+
         |                       |                       |
   DEFAULT (RESTRICT)    ON DELETE CASCADE       ON DELETE SET NULL
   - Rejects parent      - Automatically         - Updates child FK
     deletion              deletes child rows      columns to NULL
   - Raises ORA-02292    - Dangerous in cascades - Child FK must be nullable

Referential Delete Options Breakdown

Exam Trap: Oracle SQL does NOT support ON UPDATE CASCADE or ON DELETE SET DEFAULT. Only ON DELETE CASCADE and ON DELETE SET NULL are valid Oracle delete actions. Oracle also has no ON DELETE RESTRICT or ON DELETE NO ACTION keyword — restrict is simply what you get when you omit the clause entirely.

---

Constraint Naming: Explicit vs. System-Generated

Constraints can be named explicitly using the CONSTRAINT <constraint_name> clause, or left unnamed, prompting Oracle to assign a system-generated name:

-- Unnamed constraints (Oracle generates SYS_C0084321, SYS_C0084322)
CREATE TABLE demo_unnamed (
    id     NUMBER PRIMARY KEY,
    name   VARCHAR2(50) NOT NULL
);

-- Explicitly named constraints (Industry best practice)
CREATE TABLE demo_named (
    id     NUMBER        CONSTRAINT demo_pk PRIMARY KEY,
    name   VARCHAR2(50)  CONSTRAINT demo_name_nn NOT NULL
);

Why Explicit Constraint Naming Matters

1. Diagnostic Clarity: When a constraint violation occurs, Oracle reports the constraint name:

   ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated
   

   If the constraint is named SYS_C007192, identifying the offending column requires querying the data dictionary.
2. DDL Maintenance: Dropping, enabling, or disabling a constraint via ALTER TABLE requires referring to the constraint by name.