В этом учебном пособии вы узнаете, как использовать Foreign Keys в SQLite с синтаксисом и примерами.
Описание
Внешний ключ (Foreign Keys) - это способ обеспечить ссылочную целостность в вашей базе данных 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)
);
Пример
Рассмотрим пример того, как создать внешний ключ, используя оператор CREATE TABLE в SQLite.
Например:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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) ); |
В этом примере мы создали первичный ключ в таблице departments, который состоит только из одного поля department_id. Затем мы создали внешний ключ с именем fk_departments в таблице employees, который ссылается на таблицу departments на основе поля 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)
);
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 |
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) ); INSERT INTO employees SELECT * FROM _employees_old; COMMIT; PRAGMA foreign_keys=on; |
Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с внешним ключом fk_departments, который ссылается на таблицу departments на основе поля department_id. Затем он вставит все данные из таблицы _employees_old в таблицу employees.
Этот альтернативный путь позволяет добавить внешний ключ в таблицу employees, не теряя данные в таблице.