Foreign Keys with cascade delete

This Oracle tutorial explains how to use Foreign Keys with cascade delete in Oracle with syntax and examples.

What is a foreign key with Cascade DELETE in Oracle?

A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in Oracle.

A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Using a CREATE TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in Oracle/PLSQL is:

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);

Example

Let's look at an example of how to create a foreign key with cascade delete using the CREATE TABLE statement 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've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.

Because of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplier_id value.

We could also create a foreign key (with a cascade delete) 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 references the supplier table based on two fields - the supplier_id and supplier_name fields.

The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to be cascade deleted when a record in the supplier table is deleted, based on supplier_id and supplier_name.

Using an ALTER TABLE statement

Syntax

The syntax for creating a foreign key with cascade delete in an ALTER TABLE statement in Oracle/PLSQL is:

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

Example

Let's look at an example of how to create a foreign key with cascade delete using the ALTER TABLE statement in Oracle/PLSQL.

For example:

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

In this example, we've created a foreign key (with a cascade delete) called fk_supplier that references the supplier table based on the supplier_id field.

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

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