Create Foreign Keys with cascade delete SQL Server — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать внешние ключи с каскадным удалением в SQL Server с синтаксисом и примерами.

Описание

Внешний ключ с каскадным удалением означает, что если запись в родительской таблице будет удалена, то соответствующие записи в дочерней таблице будут автоматически удалены. Это называется каскадным удалением в SQL Server.

Внешний ключ с каскадным удалением может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.

Создание внешнего ключа с каскадным удалением - использование оператора CREATE TABLE

Синтаксис

Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):

CREATE TABLE child_table
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...

CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, ... child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
ON DELETE CASCADE
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
);

child_table - имя дочерней таблицы, которую вы хотите создать.
column1, column2 - столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name - имя ограничения внешнего ключа, которое вы хотите создать.
child_col1, child_col2, ... child_col_n - столбцы в child_table, которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table - имя родительской таблицы, первичный ключ которой будет использоваться в child_table.
parent_col1, parent_col2, ... parent_col3 - столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1, child_col2, ... child_col_n в child_table.
ON DELETE CASCADE - указывает, что дочерние данные удаляются при удалении родительских данных.
ON UPDATE - необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT - используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.

Пример

Давайте рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

CREATE TABLE products

( product_id INT PRIMARY KEY,

product_name VARCHAR(50) NOT NULL,

category VARCHAR(25)

);

CREATE TABLE inventory

( inventory_id INT PRIMARY KEY,

product_id INT NOT NULL,

quantity INT,

min_level INT,

max_level INT,

CONSTRAINT fk_inv_product_id

FOREIGN KEY (product_id)

REFERENCES products (product_id)

ON DELETE CASCADE

);

В этом примере внешнего ключа мы создали родительскую products. Таблица products имеет первичный ключ, который состоит из поля product_id.

Затем мы создали вторую таблицу под названием inventory, которая будет дочерней таблицей в этом примере внешнего ключа с каскадным удалением. Мы использовали оператор CREATE TABLE для создания внешнего ключа fk_inv_product_id в таблице inventory. Внешний ключ устанавливает связь между столбцом product_id в таблице inventory и столбцом product_id в таблице products.
Для этого внешнего ключа мы указали ON DELETE CASCADE, в котором SQL Server удаляет соответствующие записи в дочерней таблице, когда данные в родительской таблице удаляются. Таким образом, в этом примере, если значение product_id удаляется из таблицы products, то соответствующие записи в таблице inventory, которые используют этот product_id, также будут удалены.

Создание внешнего ключа с каскадным удалением - с помощью оператора ALTER TABLE

Синтаксис

Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора ALTER TABLE в SQL Server (Transact-SQL):

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, ... child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
ON DELETE CASCADE;

child_table - имя дочерней таблицы, которую вы хотите изменить.
fk_name - имя ограничения внешнего ключа, которое вы хотите создать.
child_col1, child_col2, ... child_col_n - столбцы в child_table, которые будут ссылаться на первичный ключ в родительской таблице.
parent_table - имя родительской таблицы, первичный ключ которой будет использоваться в child_table.
parent_col1, parent_col2, ... parent_col3 - столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1, child_col2, ... child_col_n в child_table.
ON DELETE CASCADE - указывает, что дочерние данные удаляются при удалении родительских данных.

Пример

Рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора ALTER TABLE.
Например:

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE;

ALTER TABLE inventory

ADD CONSTRAINT fk_inv_product_id

FOREIGN KEY (product_id)

REFERENCES products (product_id)

ON DELETE CASCADE;

В этом примере мы создали внешний ключ fk_inv_product_id в таблице inventory, которая ссылается на таблицу products на основе поля product_id.

Для этого внешнего ключа мы указали ON DELETE CASCADE, в котором SQL Server удаляет соответствующие записи в дочерней таблице, когда данные в родительской таблице удаляются. Таким образом, в этом примере, если значение product_id удаляется из таблицы products, соответствующие записи в таблице inventory, которые используют этот product_id, также будут удалены.