Oracle DataBase Lesson 11 – Constraints & Keys | Dataplexa

Constraints & Keys

Tables created in Lesson 10 have correct column types and nullability but nothing yet prevents duplicate customer IDs, an order referencing a customer that does not exist, or a payment amount of minus five thousand. Constraints are the mechanism Oracle uses to enforce these rules at the database level — not in application code, not in a stored procedure, but in the engine itself, so no path into the database can bypass them. This lesson adds constraints to every table in both schemas and covers how to query, disable, enable, and drop them.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Primary Keys

A primary key uniquely identifies each row in a table. Oracle enforces this with two rules automatically: the column or columns making up the key must be NOT NULL, and the values must be unique across every row. Oracle creates a unique index on the primary key columns automatically when the constraint is defined.

Primary keys can be defined inline on the column, or as a separate table-level constraint. The table-level form is preferred because it allows naming the constraint explicitly — a named constraint produces a readable error message and is far easier to manage later.

-- Inline primary key — Oracle names it automatically (system-generated name like SYS_C007293)
CREATE TABLE products (
    product_id   NUMBER(10)  PRIMARY KEY,
    product_name VARCHAR2(150) NOT NULL
);

-- Table-level named primary key — preferred in production
-- Naming convention: PK_tablename
CREATE TABLE products (
    product_id    NUMBER(10)    NOT NULL,
    product_name  VARCHAR2(150) NOT NULL,
    CONSTRAINT pk_products PRIMARY KEY (product_id)
);

-- Adding a primary key to an existing table with ALTER TABLE
ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);

-- Confirm the constraint was created
SELECT constraint_name,
       constraint_type,
       status
FROM   user_constraints
WHERE  table_name = 'CUSTOMERS';

-- ALTER TABLE: Table CUSTOMERS altered.

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
──────────────── ─────────────── ──────
PK_CUSTOMERS P ENABLED

constraint_type = 'P' indicates a primary key — Oracle uses single-letter codes: P = primary key, R = foreign key (referential), U = unique, C = check (including NOT NULL)
Oracle creates a unique index named after the constraint automatically — this index is what enforces uniqueness and also speeds up joins on the primary key column
Never rely on system-generated constraint names — when a constraint is violated Oracle reports the constraint name in the error, and SYS_C007293 tells you nothing about which rule was broken

Foreign Keys

A foreign key enforces a relationship between two tables — it guarantees that a value in a child column always matches an existing value in the parent table's primary key. An order cannot reference customer ID 999 if no customer with that ID exists. Oracle raises ORA-02291 on any insert or update that would create an orphaned reference.

By default Oracle does not cascade deletes — attempting to delete a parent row that has child rows raises ORA-02292. The ON DELETE CASCADE option changes this so that deleting a parent row automatically deletes all matching child rows. Use it deliberately — it can silently remove large amounts of data.

-- Foreign key on orders.customer_id referencing customers.customer_id
-- Naming convention: FK_childtable_parenttable
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id);

-- Foreign key with ON DELETE CASCADE
-- Deleting an order automatically deletes all its order_items
ALTER TABLE order_items
ADD CONSTRAINT fk_orderitems_orders
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id)
    ON DELETE CASCADE;

-- Foreign key referencing the products table from order_items
ALTER TABLE order_items
ADD CONSTRAINT fk_orderitems_products
    FOREIGN KEY (product_id)
    REFERENCES products (product_id);

-- Foreign key from payments to orders
ALTER TABLE payments
ADD CONSTRAINT fk_payments_orders
    FOREIGN KEY (order_id)
    REFERENCES orders (order_id);

-- View all foreign keys in the schema with their parent tables
SELECT constraint_name,
       table_name,
       r_constraint_name  -- name of the referenced primary key constraint
FROM   user_constraints
WHERE  constraint_type = 'R'
ORDER BY table_name;

CONSTRAINT_NAME TABLE_NAME R_CONSTRAINT_NAME
──────────────────────── ─────────── ─────────────────
FK_ORDERITEMS_ORDERS ORDER_ITEMS PK_ORDERS
FK_ORDERITEMS_PRODUCTS ORDER_ITEMS PK_PRODUCTS
FK_ORDERS_CUSTOMERS ORDERS PK_CUSTOMERS
FK_PAYMENTS_ORDERS PAYMENTS PK_ORDERS

Foreign keys require the parent table's primary key constraint to already exist — add all primary keys before adding foreign keys
ON DELETE CASCADE is appropriate for order_items because an order line has no meaning without its parent order; it is not appropriate where child records have independent value — use it carefully
r_constraint_name in user_constraints points to the constraint name on the parent table, not the parent table name directly — join to user_constraints again on that name to find the parent table

Unique Constraints

A unique constraint ensures no two rows have the same value in the constrained column or combination of columns. Unlike a primary key, a unique constraint allows NULL — and in Oracle, multiple rows can each have NULL in a unique column because NULL is not considered equal to anything, including another NULL.

Oracle creates a unique index to enforce the constraint, just as it does for primary keys. This index also benefits queries that filter or join on the constrained column.

-- Unique constraint on customers.email — no two customers can share an email address
-- Naming convention: UQ_tablename_columnname
ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

-- Unique constraint on employees.email in the HR schema
ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);

-- Composite unique constraint — the combination of order_id and product_id must be unique
-- This prevents the same product appearing on the same order twice as separate line items
ALTER TABLE order_items
ADD CONSTRAINT uq_orderitems_order_product UNIQUE (order_id, product_id);

-- Test that the unique constraint works
-- First insert succeeds
INSERT INTO customers (customer_id, full_name, email, country, loyalty_tier)
VALUES (1, 'Sarah Connor', 'sarah@example.com', 'USA', 'standard');

-- Second insert with the same email raises ORA-00001: unique constraint violated
INSERT INTO customers (customer_id, full_name, email, country, loyalty_tier)
VALUES (2, 'John Connor', 'sarah@example.com', 'USA', 'standard');

-- First INSERT: 1 row inserted.
-- Second INSERT: ORA-00001: unique constraint (SCHEMA.UQ_CUSTOMERS_EMAIL) violated

The error message includes the constraint name — this is why naming constraints matters; UQ_CUSTOMERS_EMAIL immediately tells you which rule was broken and on which column
A unique constraint on a single nullable column allows many NULL values — Oracle treats each NULL as distinct; this is correct behaviour and differs from some other databases
If a unique index already exists on the column, Oracle can use it to back the unique constraint rather than creating a second index

Check Constraints

A check constraint validates that a column value satisfies a specific condition every time a row is inserted or updated. The condition is any valid SQL expression that returns true or false. If it returns false the statement is rejected with ORA-02290. If it returns NULL — which happens when the column is NULL and the expression involves the column — Oracle allows the row, treating an unknown result as passing.

-- Check that unit_price is always greater than zero
ALTER TABLE products
ADD CONSTRAINT chk_products_price
    CHECK (unit_price > 0);

-- Check that stock_qty is never negative
ALTER TABLE products
ADD CONSTRAINT chk_products_stock
    CHECK (stock_qty >= 0);

-- Check that active_flag is only ever 'Y' or 'N'
ALTER TABLE products
ADD CONSTRAINT chk_products_flag
    CHECK (active_flag IN ('Y', 'N'));

-- Check that order status is one of the valid workflow values
ALTER TABLE orders
ADD CONSTRAINT chk_orders_status
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'));

-- Check that payment amount is positive
ALTER TABLE payments
ADD CONSTRAINT chk_payments_amount
    CHECK (amount > 0);

-- HR schema — salary band enforcement
-- min_salary must always be less than max_salary within a job definition
ALTER TABLE jobs
ADD CONSTRAINT chk_jobs_salary_band
    CHECK (min_salary < max_salary);

-- View all check constraints for the products table
SELECT constraint_name,
       search_condition
FROM   user_constraints
WHERE  table_name    = 'PRODUCTS'
AND    constraint_type = 'C'
AND    constraint_name NOT LIKE 'SYS_%';

CONSTRAINT_NAME SEARCH_CONDITION
───────────────────── ────────────────────────────
CHK_PRODUCTS_PRICE unit_price > 0
CHK_PRODUCTS_STOCK stock_qty >= 0
CHK_PRODUCTS_FLAG active_flag IN ('Y', 'N')

The NOT LIKE 'SYS_%' filter excludes system-generated NOT NULL constraints — Oracle stores NOT NULL as a check constraint with type C and a system-generated name, so filtering them out keeps the results clean
Check constraints cannot reference other tables or use subqueries — for cross-table validation use a foreign key or a trigger
A check constraint that references a nullable column passes when the column is NULL — if unit_price were nullable, a NULL price would bypass chk_products_price; combining NOT NULL with a check constraint closes this gap

Applying All Constraints to Both Schemas

With all four constraint types covered, here is the complete set applied to both schemas in the correct order — primary keys first, then unique constraints, then check constraints, then foreign keys last.

-- DataplexaStore — primary keys
ALTER TABLE customers   ADD CONSTRAINT pk_customers   PRIMARY KEY (customer_id);
ALTER TABLE products    ADD CONSTRAINT pk_products    PRIMARY KEY (product_id);
ALTER TABLE orders      ADD CONSTRAINT pk_orders      PRIMARY KEY (order_id);
ALTER TABLE order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_item_id);
ALTER TABLE payments    ADD CONSTRAINT pk_payments    PRIMARY KEY (payment_id);

-- DataplexaHR — primary keys
ALTER TABLE departments     ADD CONSTRAINT pk_departments     PRIMARY KEY (department_id);
ALTER TABLE jobs            ADD CONSTRAINT pk_jobs            PRIMARY KEY (job_id);
ALTER TABLE employees       ADD CONSTRAINT pk_employees       PRIMARY KEY (employee_id);
ALTER TABLE salaries        ADD CONSTRAINT pk_salaries        PRIMARY KEY (salary_id);
ALTER TABLE payroll_history ADD CONSTRAINT pk_payroll_history PRIMARY KEY (payroll_id);

-- Unique constraints
ALTER TABLE customers  ADD CONSTRAINT uq_customers_email  UNIQUE (email);
ALTER TABLE employees  ADD CONSTRAINT uq_employees_email  UNIQUE (email);

-- Check constraints — DataplexaStore
ALTER TABLE products ADD CONSTRAINT chk_products_price CHECK (unit_price > 0);
ALTER TABLE products ADD CONSTRAINT chk_products_stock CHECK (stock_qty >= 0);
ALTER TABLE products ADD CONSTRAINT chk_products_flag  CHECK (active_flag IN ('Y','N'));
ALTER TABLE orders   ADD CONSTRAINT chk_orders_status  CHECK (status IN ('pending','processing','shipped','delivered','cancelled'));
ALTER TABLE payments ADD CONSTRAINT chk_payments_amount CHECK (amount > 0);

-- Check constraints — DataplexaHR
ALTER TABLE jobs            ADD CONSTRAINT chk_jobs_salary_band   CHECK (min_salary < max_salary);
ALTER TABLE payroll_history ADD CONSTRAINT chk_payroll_net        CHECK (net_pay >= 0);

-- Foreign keys — DataplexaStore (all parent PKs exist before this point)
ALTER TABLE orders      ADD CONSTRAINT fk_orders_customers      FOREIGN KEY (customer_id)  REFERENCES customers (customer_id);
ALTER TABLE order_items ADD CONSTRAINT fk_orderitems_orders     FOREIGN KEY (order_id)     REFERENCES orders (order_id) ON DELETE CASCADE;
ALTER TABLE order_items ADD CONSTRAINT fk_orderitems_products   FOREIGN KEY (product_id)   REFERENCES products (product_id);
ALTER TABLE payments    ADD CONSTRAINT fk_payments_orders        FOREIGN KEY (order_id)     REFERENCES orders (order_id);

-- Foreign keys — DataplexaHR
ALTER TABLE departments     ADD CONSTRAINT fk_departments_manager   FOREIGN KEY (manager_id)    REFERENCES employees (employee_id);
ALTER TABLE employees       ADD CONSTRAINT fk_employees_jobs        FOREIGN KEY (job_id)        REFERENCES jobs (job_id);
ALTER TABLE employees       ADD CONSTRAINT fk_employees_departments FOREIGN KEY (department_id) REFERENCES departments (department_id);
ALTER TABLE employees       ADD CONSTRAINT fk_employees_manager     FOREIGN KEY (manager_id)    REFERENCES employees (employee_id);
ALTER TABLE salaries        ADD CONSTRAINT fk_salaries_employees    FOREIGN KEY (employee_id)   REFERENCES employees (employee_id);
ALTER TABLE payroll_history ADD CONSTRAINT fk_payroll_employees     FOREIGN KEY (employee_id)   REFERENCES employees (employee_id);

-- DataplexaStore primary keys: 5 constraints created.
-- DataplexaHR primary keys: 5 constraints created.
-- Unique constraints: 2 constraints created.
-- Check constraints: 7 constraints created.
-- Foreign keys: 10 constraints created.

The circular dependency between departments and employees — departments reference a manager who is an employee, employees reference a department — is resolved by adding foreign keys after both tables and their primary keys exist; the tables themselves can be created in any order as long as the foreign keys come last
The self-referencing fk_employees_manager foreign key is valid in Oracle — a table can reference itself as long as the primary key on the same table exists first
Adding foreign keys to tables that already contain data will fail if any existing values violate the constraint — always validate data integrity before adding constraints to populated tables

Managing Constraints

Constraints can be disabled temporarily without being dropped — useful during bulk data loads where enforcing every constraint on every row would be slow. Once the load is complete the constraint is re-enabled and Oracle validates all rows in one pass.

-- Disable a constraint — Oracle stops enforcing it but keeps the definition
ALTER TABLE orders
DISABLE CONSTRAINT fk_orders_customers;

-- Enable a constraint — Oracle re-validates all existing rows before enabling
-- Fails with ORA-02293 if any existing row violates the constraint
ALTER TABLE orders
ENABLE CONSTRAINT fk_orders_customers;

-- Drop a constraint permanently
ALTER TABLE orders
DROP CONSTRAINT fk_orders_customers;

-- View all constraints on a table with their type and status
SELECT constraint_name,
       constraint_type,
       status,
       validated
FROM   user_constraints
WHERE  table_name = 'ORDERS'
ORDER BY constraint_type;

CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED
──────────────────── ─────────────── ─────── ─────────
CHK_ORDERS_STATUS C ENABLED VALIDATED
FK_ORDERS_CUSTOMERS R ENABLED VALIDATED
PK_ORDERS P ENABLED VALIDATED

validated = 'VALIDATED' means Oracle has checked all existing rows against the constraint — if you re-enable a disabled constraint it will show NOT VALIDATED until Oracle completes the validation pass
Disabling a foreign key does not disable the primary key it references — both sides must be managed independently
Dropping a primary key that is referenced by a foreign key raises ORA-02273 — use DROP CONSTRAINT pk_name CASCADE to drop the primary key and all dependent foreign keys in one statement

Summary

Constraint	Type Code	What It Enforces	Error on Violation
PRIMARY KEY	P	Unique, NOT NULL — one per table	ORA-00001
FOREIGN KEY	R	Child value must exist in parent primary key	ORA-02291 / ORA-02292
UNIQUE	U	No duplicate values — NULLs are allowed and distinct	ORA-00001
CHECK	C	Column value must satisfy a SQL expression	ORA-02290
NOT NULL	C	Column must always have a value	ORA-01400
DISABLE CONSTRAINT	—	Suspends enforcement — definition kept	—
ENABLE CONSTRAINT	—	Re-enables and re-validates all existing rows	ORA-02293 if rows violate it

Practice Questions

Practice 1. Why should constraints always be given explicit names rather than relying on Oracle to generate them?

Practice 2. What is the difference between ORA-02291 and ORA-02292 in the context of foreign keys?

Practice 3. A check constraint is added to ensure unit_price > 0. A row is inserted with unit_price = NULL. Does the constraint reject the row?

Practice 4. Why is ON DELETE CASCADE used on fk_orderitems_orders but not on fk_orders_customers?

Practice 5. What does the validated column in user_constraints tell you, and when would it show NOT VALIDATED?

Quiz

Quiz 1. Which constraint type code does Oracle use for foreign keys in user_constraints?

R
F
K
P

Quiz 2. A unique constraint is added to employees.email. Two rows are inserted with email = NULL. What happens?

Both rows are inserted — Oracle allows multiple NULLs in a unique column
The second insert fails with ORA-00001
Both inserts fail because the column must be NOT NULL
Oracle stores only one NULL and ignores the second

Quiz 3. You attempt to drop the primary key on the customers table but it is referenced by a foreign key on orders. What error does Oracle raise?

ORA-02273
ORA-02292
ORA-00001
ORA-01400

Quiz 4. Which of the following is a valid use of a check constraint?

CHECK (status IN ('pending','processing','shipped'))
CHECK (customer_id IN (SELECT customer_id FROM customers))
CHECK (order_date > SYSDATE)
CHECK (total_amount = SUM(unit_price * quantity))

Quiz 5. What is the correct order for applying constraints to a new schema?

Primary keys → unique → check → foreign keys
Foreign keys → primary keys → check → unique
Check → unique → foreign keys → primary keys
Any order — Oracle resolves dependencies automatically

Next up — Sequences & Identity Columns — Learn how Oracle generates unique primary key values automatically using sequences and the NEXTVAL pattern used across both schemas.

← Previous Course Index Next →

Oracle Database