В этом учебном пособии вы узнаете, как использовать Foreign Keys with cascade delete в SQLite с синтаксисом и примерами.
Описание
Внешний ключ с каскадным удалением (Foreign Keys with cascade delete) означает, что если запись в родительской таблице будет удалена, то соответствующие записи в дочерней таблице будут автоматически удалены. Это называется каскадным удалением в SQLite.
Внешний ключ с каскадным удалением может быть определен только в операторе CREATE TABLE.
Подсказка: Вы не можете добавить внешний ключ с каскадным удалением в таблицу используя ALTER TABLE, потому что SQLite не поддерживает ADD CONSTRAINT в операторе ALTER TABLE. Однако позже в этом руководстве мы покажем вам альтернативный путь, который позволит вам добавить внешний ключ с каскадным удалением к существующей таблице.
Как создать внешний ключ с каскадным удалением с помощью оператора CREATE TABLE
Синтаксис
Синтаксис для создания внешний ключ с каскадным удалением с использованием оператора 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 CASCADE
);
Пример
Рассмотрим пример того, как создать внешний ключ с каскадным удалением с помощью оператора 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 CASCADE ); |
В этом примере мы создали первичный ключ в таблице departments, который состоит только из одного поля - department_id. Затем мы создали внешний ключ с именем fk_departments в таблице employees, который ссылается на таблицу departments на основе поля department_id.
Из-за каскадного удаления при удалении записи в таблице departments также будут удалены все записи в таблице employees, имеющие одинаковое значение department_id.
Как добавить внешний ключ с каскадным удалением к существующей таблице
Вы не можете использовать оператор ALTER TABLE для добавления внешнего ключа с каскадным удалением в SQLite. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу с внешним ключом, а затем скопировать данные в новую таблицу.
Синтаксис
Синтаксис для добавления внешнего ключа с каскадным удалением к существующей таблице в 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 CASCADE
);
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); |
Теперь давайте добавим внешний ключ с каскадным удалением в таблицу 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 CASCADE ); INSERT INTO employees SELECT * FROM _employees_old; COMMIT; PRAGMA foreign_keys=on; |
В этом примере мы создали внешний ключ (с каскадным удалением) с именем fk_departments, который ссылается на таблицу departments на основе поля department_id.
Теперь давайте продемонстрируем, как работает каскадное удаление. В настоящее время у нас есть следующие записи в таблице 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 равно 30.
После каскадного удаления таблица employees будет выглядеть так:
| employee_id | last_name | first_name | department_id |
|---|---|---|---|
| 10001 | Anderson | Gans | 999 |