Using Foreign Keys with Cascade Delete in Oracle

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

What is a overseas key with Cascade DELETE in Oracle?

A foreign key with cascade delete ability that if a document in the parent desk is deleted, then the corresponding archives in the infant table will mechanically be deleted. This is referred to as a cascade delete in Oracle.

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

Using a CREATE TABLE statement

Syntax

The syntax for growing a overseas key with cascade delete the use of a CREATE TABLE announcement 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 instance of how to create a foreign key with cascade delete the usage of the CREATE TABLE announcement 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 major key on the supplier table known as supplier_pk. It consists of only one subject – the supplier_id field. Then we have created a foreign key known as fk_supplier on the products table that references the provider desk based on the supplier_id field.

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

We should also create a foreign key (with a cascade delete) with extra than one field as in the instance 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 overseas key known as fk_foreign_comp references the provider table based on two fields – the supplier_id and supplier_name fields.

The cascade delete on the overseas key called fk_foreign_comp reasons all corresponding records in the merchandise desk to be cascade deleted when a document in the supplier desk is deleted, based totally on supplier_id and supplier_name.

Using an ALTER TABLE statement

Syntax

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

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

Example

Let’s seem at an example of how to create a foreign key with cascade delete using the ALTER TABLE announcement 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’ve got created a overseas key (with a cascade delete) referred to as fk_supplier that references the supplier desk primarily based on the supplier_id field.

We may want to also create a overseas key (with a cascade delete) with more than one discipline 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;