Oracle delete cascade – Foreign Keys with cascade delete

You will learn how to use foreign keys with cascade delete in Oracle PLSQL with syntax and examples.

What are foreign keys with cascade delete?

Foreign keys with cascade delete means that if a parent table entry is deleted, the corresponding entries in the child table will be automatically deleted. This is called cascade deletion in Oracle.

Foreign keys with cascade delete can be defined either in CREATE TABLE or ALTER TABLE.

Using CREATE TABLE

Syntax for creating Foreign keys with cascade delete using the CREATE TABLE operator in Oracle/PLSQL:

Syntax for creating Foreign keys with cascade delete

CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
…
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);

Let’s consider an example of how to create foreign keys with cascade delete using the CREATE TABLE operator in Oracle/PLSQL.

For example:

CREATE TABLE supplier
( supplier_id numeric(10) >not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE
);

In this example we created the primary key of the supplier table called supplier_pk. It consists of only one supplier_id field. Then we created foreign keys (foreign key) with the name fk_supplier in the products table, which refers to the supplier table, based on the supplier_id field.

So, when a record in the supplier table is cascaded, all records in the products table that have the same value in the supplier_id field will also be removed.

We could also create a foreign key (with cascading) with more than one field as in the example below:

CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) >not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE
);

In this example, our foreign key called fk_foreign_comp refers to the supplier table based on two fields supplier_id and supplier_name.

Cascading the foreign key named fk_foreign_comp will cause all relevant entries in the products table to be cascaded when the entry in the supplier table based on the supplier_id and supplier_name fields is removed.

Using ALTER TABLE

Syntax for creating foreign keys with cascade delete using ALTER TABLE operator in Oracle/PLSQL:

Syntax for creating foreign keys

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;

Let’s consider an example of how to create foreign keys with cascade delete using the ALTER TABLE operator in Oracle/PLSQL.

For example:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;

In this example we created foreign keys (with cascade deletion) called fk_supplier, which refers to the supplier table based on the supplier_id field.

We could also create foreign keys (cascaded) with more than one field, as in the example below:

ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;

SQL tutorials: On Delete Cascade Foreign Key