В этом учебном пособии вы узнаете, как использовать Foreign Keys with "set null on delete" в SQLite с синтаксисом и примерами.
Описание
Внешний ключ с «set null on delete» означает, что если запись в родительской таблице будет удаляться, то соответствующие записи в дочерней таблице, которые будут иметь поля внешнего ключа, равные null, то записи в дочерней таблице не будут удалены.
Внешний ключ с «set null on delete» может быть определен только в операторе CREATE TABLE.
Подсказка: Вы не можете добавить внешний ключ с «set null on delete» к таблице, используя ALTER TABLE, потому что SQLite не поддерживает ADD CONSTRAINT в операторе ALTER TABLE. Однако позже в этом руководстве мы покажем вам альтернативный путь, который позволит вам добавить внешний ключ с «set null on delete» к существующей таблице.
Как создать внешний ключ с «Set Null on Delete» с помощью оператора CREATE TABLE
Синтаксис
Синтаксис для создания внешнего ключа с «set null on delete» с помощью оператора CREATE TABLE в SQLite:
(
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
);
Пример
Рассмотрим пример того, как создать внешний ключ с «set null on delete», используя оператора CREATE TABLE в SQLite.
Например:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE departments ( department_id INTEGER PRIMARY KEY AUTOINCREMENT, department_name VARCHAR ); CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, department_id INTEGER, CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL ); |
В этом примере мы создали первичный ключ в таблице departments, который состоит только из одного поля - поля department_id. Затем мы создали внешний ключ с именем fk_departments в таблице employees, который ссылается на таблицу departments на основе поля department_id.
Из-за опции ON DELETE SET NULL, когда запись в таблице departments удаляется, все соответствующие записи в таблице employees будут иметь для department_id значение NULL.
Как добавить внешний ключ с «set null on delete» в существующую таблицу
Вы не можете использовать оператор ALTER TABLE для добавления внешнего ключа с «set null on delete» в SQLite. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу с внешним ключом, а затем скопировать данные в новую таблицу.
Синтаксис
Синтаксис для добавления внешнего ключа с «set null on delete» к существующей таблице в SQLite:
BEGIN TRANSACTION;
ALTER TABLE table1 RENAME TO _table1_old;
CREATE TABLE table1
(
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
);
INSERT INTO table1 SELECT * FROM _table1_old;
COMMIT;
PRAGMA foreign_keys=on;
Пример
Во-первых, давайте начнем с создания наших 2 таблиц (departments и employees):
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE departments ( department_id INTEGER PRIMARY KEY AUTOINCREMENT, department_name VARCHAR ); CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, department_id INTEGER ); |
Далее давайте добавим некоторые данные в эти таблицы:
|
1 2 3 4 5 |
INSERT INTO departments VALUES (30, 'IT'); INSERT INTO departments VALUES (999, 'Sales'); INSERT INTO employees VALUES (10000, 'Smith', 'Samanta', 30); INSERT INTO employees VALUES (10001, 'Anderson', 'Gans', 999); |
Теперь давайте добавим внешний ключ с ON DELETE SET NULL в таблицу employees:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
PRAGMA foreign_keys=off; BEGIN TRANSACTION; ALTER TABLE employees RENAME TO _employees_old; CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY AUTOINCREMENT, last_name VARCHAR NOT NULL, first_name VARCHAR, department_id INTEGER, CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL ); INSERT INTO employees SELECT * FROM _employees_old; COMMIT; PRAGMA foreign_keys=on; |
В этом примере мы создали внешний ключ c (set null on delete) с именем fk_departments, который ссылается на таблицу departments на основе поля department_id.
Теперь давайте продемонстрируем, как работает ON DELETE SET NULL. В настоящее время у нас есть следующие записи в таблице employees:
| employee_id | last_name | first_name | department_id |
|---|---|---|---|
| 10000 | Smith | Samanta | 30 |
| 10001 | Anderson | Gans | 999 |
Теперь давайте удалим одну из записей из таблицы departments и посмотрим, что произойдет:
|
1 2 |
DELETE FROM departments WHERE department_id = 30; |
Даже несмотря на то, что мы удаляем запись из таблицы departments, где для department_id установлено значение 30, внешний ключ (с установленным нулевым значением при удалении) обновит таблицу employees и изменит все значения department_id на NULL, где для значения department_id будет 30.
После удаления таблица сотрудников будет выглядеть так:
| employee_id | last_name | first_name | department_id |
|---|---|---|---|
| 10000 | Smith | Samanta | Null |
| 10001 | Anderson | Gans | 999 |