В этом учебном пособии вы узнаете, как использовать внешние ключи с «set null on delete» в SQL Server с синтаксисом и примерами.
Описание
Внешний ключ с «set null on delete» означает, что если будет удалена запись в родительской таблице, и соответствующие записи в дочерней таблице будут иметь поля внешнего ключа со значениями NULL, то записи в дочерней таблице не будут удалены в SQL Server.
Внешний ключ с «set null on delete» может быть создан с помощью оператора CREATE TABLE или оператора ALTER TABLE.
Создать внешний ключ с «set null on delete» - использование оператора CREATE TABLE
Синтаксис
Синтаксис создания внешнего ключа с установленным значением null для удаления с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):
(
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 SET NULL
[ 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 SET NULL - указывает, что дочерние данные установлены в NULL, когда родительские данные удаляются. Детские данные НЕ удаляются.
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. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.
Пример
Рассмотрим пример создания внешнего ключа с «set null on delete» в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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, quantity INT, min_level INT, max_level INT, CONSTRAINT fk_inv_product_id FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE SET NULL ); |
В этом примере мы создали родительскую таблицу products. Таблица products имеет первичный ключ, который состоит из поля product_id.
Затем мы создали вторую таблицу под названием inventory, которая будет дочерней таблицей в этом примере. Мы использовали оператор CREATE TABLE для создания внешнего ключа fk_inv_product_id в таблице inventory. Внешний ключ устанавливает связь между столбцом product_id в таблице inventory и столбцом product_id в таблице products.
Для этого внешнего ключа мы указали опцию ON DELETE SET NULL, которая сообщает SQL Server установить в NULL соответствующие записи в дочерней таблице, когда данные в родительской таблице будут удалены. Поэтому в этом примере, если значение product_id будет удалено из таблицы products, то соответствующие записи в таблице inventory, которые используют этот product_id, будут иметь значение product_id равным NULL.
Теперь одна важная вещь в настройке этого внешнего ключа !!! Поскольку этот внешний ключ установит поле product_id равным NULL при удалении в таблице inventory, вам нужно убедиться, что вы установили столбец product_id в дочерней таблице в столбец с нулевым значением. Если вы установите столбец NOT NULL в операторе CREATE TABLE, вы получите следующее сообщение об ошибке:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 SET NULL ); Msg 1761, Level 16, State 0, Line 1 Cannot create the foreign key "fk_inv_product_id" with the SET NULL referential action, because one or more referencing columns are not nullable. Msg 1750, Level 16, State 0, Line 1 Could not create constraint or index. See previous errors. |
Поэтому обязательно определите product_id в таблице inventory как поле со значением NULL, следующим образом, чтобы SQL Server мог установить столбец в NULL, когда необходимо ограничение внешнего ключа.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE inventory ( inventory_id INT PRIMARY KEY, product_id INT, quantity INT, min_level INT, max_level INT, CONSTRAINT fk_inv_product_id FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE SET NULL ); Command(s) completed successfully. |
Создать внешний ключ с «set null on delete» - с помощью оператора ALTER TABLE
Синтаксис
Синтаксис создания внешнего ключа с «set null on delete» с использованием оператора ALTER TABLE в SQL Server (Transact-SQL):
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 SET NULL;
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 SET NULL - указывает, что дочерние данные установлены в NULL, когда родительские данные удаляются. Дочерние данные НЕ удаляются.
Пример
Рассмотрим пример создания внешнего ключа с «set null on delete» в SQL Server (Transact-SQL) с помощью оператора ALTER TABLE.
Например:
|
1 2 3 4 5 |
ALTER TABLE inventory ADD CONSTRAINT fk_inv_product_id FOREIGN KEY (product_id) REFERENCES products (product_id) ON DELETE SET NULL; |
В этом примере внешнего ключа мы создали внешний ключ fk_inv_product_id в таблице inventory, который ссылается на таблицу products на основе поля product_id.
Для этого внешнего ключа мы указали предложение ON DELETE SET NULL, которое сообщает SQL Server обновлять соответствующие записи в дочерней таблице до NULL, когда данные в родительской таблице удаляются. Поэтому в этом примере, если значение product_id удаляется из таблицы products, то соответствующие записи в таблице inventory, которые используют этот product_id, будут иметь столбец product_id равным NULL.