ExamFinch.com My Study

Study guide

11.2 Integrity Constraints

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

11.2 Integrity Constraints

Key Takeaways Explain This Topic AI

Oracle supports five declarative integrity constraint types: PRIMARY KEY, FOREIGN KEY (REFERENCES), UNIQUE, NOT NULL, and CHECK.

PRIMARY KEY enforces non-null uniqueness (1 per table, max 32 columns) and automatically creates a unique index; UNIQUE permits multiple NULL values in standard columns.

CHECK constraints validate boolean conditions per row; they strictly prohibit subqueries, SYSDATE, sequence pseudocolumns (CURRVAL/NEXTVAL), and PL/SQL functions.

Composite constraints spanning multiple columns MUST be declared out-of-line (table level), whereas NOT NULL constraints MUST be declared inline (column level).

Referential delete actions (ON DELETE CASCADE and ON DELETE SET NULL) dictate child row behavior when parent records are removed, but Oracle does not support ON UPDATE CASCADE.

Last updated: August 2026

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

Constraint Type Primary Function NULL Handling Underlying Index Limit per Table

PRIMARY KEY Uniquely identifies each row in the table (Entity Integrity). Rejects all NULLs. Automatically creates a unique B-tree index (or uses an existing index). Maximum 1 per table (up to 32 columns).

FOREIGN KEY ( REFERENCES ) Enforces referential relationship to a parent table's PK or UK (Referential Integrity). Allows NULLs (unless column has NOT NULL ). No automatic index created (manual index highly recommended to prevent table locks). Unlimited.

UNIQUE Prevents duplicate non-null values in a column or set of columns. Allows multiple NULLs (standard single-column or composite where all parts are NULL). Automatically creates a unique B-tree index . Unlimited.

NOT NULL Guarantees that a column cannot contain missing or unknown data. Rejects NULLs. None. Unlimited.

CHECK Enforces a boolean condition on row values (Domain Integrity). Passes if condition evaluates to TRUE or UNKNOWN (NULL) . Fails only on FALSE . None. Unlimited.

Inline vs. Out-of-Line Constraint Syntax

Oracle allows constraints to be declared in two syntactical positions:

Inline (Column-Level): Defined immediately following the column datatype definition, before the comma. Suitable for single-column constraints.

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:

Subqueries and Scalar Queries: CHECK (dept_id IN (SELECT id FROM depts)) → \rightarrow → ORA-02251 .

Non-Deterministic Date Functions: CHECK (hire_date <= SYSDATE) or CHECK (created_at >= CURRENT_DATE) → \rightarrow → ORA-02436 .

Sequence Pseudocolumns: CHECK (order_id > order_seq.CURRVAL) or NEXTVAL → \rightarrow → ORA-02287 .

Environment / Session Functions: CHECK (created_by = USER) or UID , USERENV , SYS_CONTEXT → \rightarrow → ORA-02436 .

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 .

User-Defined PL/SQL Functions: Custom stored functions cannot be referenced in a CHECK condition.

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

Delete Action Syntax Child Table Behavior Requirements

RESTRICT / NO ACTION (Default) (Omit ON DELETE clause) Rejects parent deletion with ORA-02292 if matching child rows exist. None.

CASCADE ON DELETE CASCADE Automatically deletes all child rows referencing the deleted parent row. Cascade deletes can traverse multiple hierarchical levels.

SET NULL ON DELETE SET NULL Automatically sets the foreign key column in all matching child rows to NULL . Child foreign key column must allow NULLs (cannot have NOT NULL ).

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

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.

DDL Maintenance: Dropping, enabling, or disabling a constraint via ALTER TABLE requires referring to the constraint by name.