Using Foreign Keys with Set Null on Delete in Oracle

This Oracle tutorial explains how to use Foreign Keys with “set null on delete” in Oracle with syntax and examples.

What is a overseas key with “Set NULL on Delete” in Oracle?

A foreign key with “set null on delete” capacity that if a record in the dad or mum desk is deleted, then the corresponding data in the toddler table will have the foreign key fields set to null. The information in the baby table will now not be deleted.

A foreign key with a “set null on delete” can be defined in both a CREATE TABLE announcement or an ALTER TABLE statement.

Using a CREATE TABLE statement

Syntax

The syntax for growing a overseas key using a CREATE TABLE assertion 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 SET NULL
);

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),
  CONSTRAINT fk_supplier
    FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id)
    ON DELETE SET NULL
);

In this example, we have created a foremost key on the dealer table referred to as supplier_pk. It consists of solely one subject – the supplier_id field. Then we’ve created a overseas key known as fk_supplier on the products table that references the dealer table based on the supplier_id field.

Because of the set null on delete, when a file in the dealer desk is deleted, all corresponding documents in the products table will have the supplier_id values set to null.

We should also create a overseas key “set null on 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),
  supplier_name varchar2(50),
  CONSTRAINT fk_supplier_comp
    FOREIGN KEY (supplier_id, supplier_name)
    REFERENCES supplier(supplier_id, supplier_name)
    ON DELETE SET NULL
);

In this example, our overseas key known as fk_foreign_comp references the provider desk based totally on two fields – the supplier_id and supplier_name fields.

The delete on the overseas key referred to as fk_foreign_comp reasons all corresponding information in the merchandise table to have the supplier_id and supplier_name fields set to null when a report in the supplier table is deleted, primarily based on supplier_id and supplier_name.

Using an ALTER TABLE statement

Syntax

The syntax for creating a overseas key in an ALTER TABLE assertion is:

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

Example

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

In this example, we’ve got created a overseas key “with a set null on delete” known as fk_supplier that references the dealer table based on the supplier_id field.

We may want to also create a foreign key “with a set null on delete” with more than one subject as in the instance below:

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