How to Use Foreign Key ON DELETE Actions in Oracle

A foreign key guarantees that every child row points to an existing parent. That raises a question the moment someone deletes a parent: what happens to its children? Oracle offers three answers. By default the delete is refused; ON DELETE CASCADE deletes the children too; ON DELETE SET NULL keeps them and clears their reference.

Code for This Guide

The main examples are in the examples/tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

column type references parent_table [(column)] [on delete {cascade | set null}]

constraint name foreign key (column) references parent_table (column)
  [on delete {cascade | set null}]

The Default: Refuse

Example:

-- without a delete action, a parent with children cannot be deleted
delete from routes where route_id = 1;

Output:

Error starting at line : 2
In command -
delete from routes where route_id = 1
Error report -
ORA-02292: integrity constraint (NIMBUS.FLIGHTS_ROUTE_FK) violated - child
record found

Route 1 has flights, so it cannot be deleted while FLIGHTS references it.

ON DELETE CASCADE

The cascade example uses a FARE_RULES table, created first:

Example:

create table fare_rules (
  rule_id       number        constraint fare_rules_pk primary key,
  route_id      number        not null constraint fare_rules_route_fk references routes,
  cabin         varchar2(8)   default 'ECONOMY' not null,
  valid_from    date          not null,
  valid_to      date,
  base_fare     number(8,2)   not null,
  refundable    boolean       default false,
  notes         varchar2(200),
  constraint fare_rules_cabin_ck check (cabin in ('BUSINESS', 'ECONOMY')),
  constraint fare_rules_dates_ck check (valid_to is null or valid_to > valid_from),
  constraint fare_rules_uk unique (route_id, cabin, valid_from)
);

insert into fare_rules (rule_id, route_id, valid_from, base_fare)
values (1, 1, date '2026-04-01', 520);

select rule_id, route_id, cabin, valid_from, base_fare, refundable from fare_rules;

Output:

Table FARE_RULES created.

1 row inserted.

   RULE_ID    ROUTE_ID CABIN      VALID_FROM        BASE_FARE REFUNDABLE
__________ ___________ __________ ______________ ____________ _____________
         1           1 ECONOMY    01-APR-2026             520 false

Notes that belong to a fare rule are deleted with it.

Example:

create table rule_notes (
  note_id  number primary key,
  rule_id  number constraint rule_notes_rule_fk references fare_rules on delete cascade,
  note     varchar2(100)
);
insert into rule_notes values (1, 1, 'Winter promotion'), (2, 1, 'Excludes holidays');

delete from fare_rules where rule_id = 1;
select count(*) as notes_left from rule_notes;
rollback;

Output:

Table RULE_NOTES created.

2 rows inserted.

1 row deleted.

   NOTES_LEFT
_____________
            0

Rollback complete.

Deleting the rule deletes both notes; the ROLLBACK undoes it all.

ON DELETE SET NULL

Example:

delete from notes_parent where id = 1;
select id, parent_id from notes_child;
rollback;

Output:

1 row deleted.

   ID    PARENT_ID
_____ ____________
   10

Rollback complete.

The child row stays, with its parent_id cleared. The foreign key column must allow NULLs for this action.

Things to Know

• Index foreign key columns: without an index, deleting a parent row locks the whole child table while Oracle checks for children.
• Cascades can chain through several levels of tables, which makes a single DELETE remove far more than it seems; use CASCADE only where children truly belong to the parent.
• Oracle has no ON UPDATE CASCADE; primary keys should not change, which is one reason to use surrogate keys.

Related Guides

• How to Use Deferrable Constraints in Oracle
• How to Create Identity Columns in Oracle

Conclusion

A foreign key's ON DELETE action decides what happens to child rows when their parent is deleted: refuse by default, delete them with CASCADE, or clear their reference with SET NULL. Choose by whether the children can exist on their own, and index the foreign key columns.