Foreign Keys with set null on delete — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать в Oracle/PLSQL foreign keys with "set null on delete" с синтаксисом и примерами.

Что такое foreign keys with "set null on delete" в Oracle?

Foreign keys with "set null on delete" означает, что если запись в родительской таблице удаляется, и соответствующие записи в дочерней таблице, имеющие foreign key поле со значением NULL, то записи в дочерней таблице не могут быть удалены.
Foreign keys with "set null on delete" может быть определен в любом операторе CREATE TABLE или операторе ALTER TABLE.

Использование оператора CREATE TABLE.

Синтаксис

Синтаксис для создания foreign keys with "set null on delete" с помощью оператора CREATE TABLE в Oracle/PLSQL:

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

Пример

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

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

);

В этом примере мы создали primary key таблицы supplier под названием supplier_pk. Она состоит только из одного поля supplier_id. Затем мы создали foreign key с наименованием fk_supplier в таблице products, который ссылается на таблицу supplier, на основании поля supplier_id.
Если удаляется запись с NULL значением в таблице supplier, то соответствующая запись с supplier_id в таблице products примет значения NULL.
Мы могли бы также создать foreign keys with "set null on delete" с более чем одним полем, как в примере ниже:

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

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

);

В этом примере, foreign keys называется fk_foreign_comp ссылается на таблицу supplier на основе двух полей: supplier_id и supplier_name.
При удалении с foreign keys fk_foreign_comp всех соответствующих записей таблицы products имеющих поля supplier_id и supplier_name, им присваивается значение NULL, когда удаляется запись в таблице supplier, основанная на supplier_id и supplier_name.

Использование оператора ALTER TABLE.

Синтаксис

Синтаксис для создания foreign keys с помощью оператора ALTER TABLE:

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;

Пример

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

ALTER TABLE products

ADD CONSTRAINT fk_supplier