SQLite foreign key

7 minute read

В заданиях 11 раздела создается база данных с двумя таблицами: dhcp и switches.

Все файлы для этой заметки находятся в репозитории курса.

Таблица dhcp (показана часть записей):

mac ip vlan interface switch
00:07:BC:3F:A6:50 10.1.10.6 10 FastEthernet0/3 sw1
00:09:BC:3F:A6:50 192.168.10 1 FastEthernet0/7 sw1
00:A9:BB:3D:D6:58 10.1.10.20 10 FastEthernet0/7 sw2
00:B4:A3:3E:5B:69 10.1.5.20 5 FastEthernet0/5 sw2
00:E9:BC:3F:A6:50 100.1.1.6 3 FastEthernet0/2 sw3

Таблица switches:

hostname location
sw1 London, 21 New Globe Walk
sw2 London, 21 New Globe Walk
sw3 London, 21 New Globe Walk

По смыслу заданий столбец switch в таблице dhcp указывает на коммутатор, на котором была найдена запись. При этом, в таблице switches указан соответствующий коммутатор и его расположение. То есть, между таблицами есть некая связь.

В SQL есть возможность описывать подобные связи между таблицами. Например, можно сделать так, что при удалении коммутатора из таблицы switches будут удаляться все записи таблицы dhcp, у которых в столбце switch указан этот коммутатор.

Связь между таблицами создается с помощью внешнего ключа (foreign key).

Внешний ключ указывает каким образом связаны таблицы в базе данных, а также контролирует изменение данных.

Пример создания внешнего ключа (файл dhcp_snooping_schema_ver1.sql):

create table switches (
    hostname    text not null primary key,
    location    text
);

create table dhcp (
    mac          text primary key,
    ip           text,
    vlan         text,
    interface    text,
    switch       text not null,
    FOREIGN KEY (switch) references switches(hostname)
);

В задании используется другой вариант указания внешнего ключа - в поле switch: switch text not null references switches(hostname). Это просто более короткий вариант такого же правила.

При такой настройке столбец switch в таблице dhcp является внешним ключом. Он указывает на столбец hostname в таблице switches.

Как правило, внешний ключ ссылается на primary key, но может также ссылаться на поле с ограничением UNIQUE.

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

Подготовка

Для того чтобы посмотреть на внешний ключ в действии, надо добавить данные в таблицы.

Для начала надо создать таблицы:

$ sqlite3 dhcp_snooping.db

sqlite> .read dhcp_snooping_schema_ver1.sql

sqlite> .tables
dhcp      switches

После выполнения команд выше, надо заполнить таблицы. Это можно сделать импортировав данные из подготовленных CSV файлов:

sqlite> .mode csv
sqlite> .import dhcp.csv dhcp
sqlite> .import switches.csv switches
sqlite> .mode column

Таблица switches:

sqlite> select * from switches;
hostname    location
----------  -------------------------
sw1         London, 21 New Globe Walk
sw2         London, 21 New Globe Walk
sw3         London, 21 New Globe Walk

Таблица dhcp:

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2

Внешний ключ

В sqlite3 по умолчанию выключено соблюдение ограничений внешнего ключа. Для включения используется такая команда:

sqlite> PRAGMA foreign_keys = ON;

Команда PRAGMA foreign_keys = ON; должна указываться для каждого подключения к БД.

Теперь, если попробовать добавить в таблицу dhcp запись с коммутатором, которого нет в таблице switches, возникнет ошибка:

sqlite> INSERT into dhcp
   ...> values ('00:A9:0C:4F:55:50', '10.1.3.1', '10', 'FastEthernet0/19', 'sw4');
Error: FOREIGN KEY constraint failed

Внешний ключ не дает добавлять данные в таблицу dhcp из-за того, что в таблице switches не существует коммутатора sw4. Если добавить коммутатор и повторить команду, она отработает.

Кроме того, нельзя удалить коммутатор из таблицы switches, если в таблице dhcp есть записи, которые указывают на него:

sqlite> delete from switches where hostname = 'sw2';
Error: FOREIGN KEY constraint failed

Очень важно не забывать включать ограничение. Иначе, несмотря на наличие внешнего ключа, он не будет работать.

Например, можно отключить поддержку внешнего ключа и попытаться выполнить команду еще раз:

sqlite> PRAGMA foreign_keys = OFF;

sqlite> INSERT into dhcp
   ...> values ('00:A9:0C:4F:55:50', '10.1.3.1', '10', 'FastEthernet0/19', 'sw4');

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2
00:A9:0C:4F:55:50  10.1.3.1    10          FastEthernet0/1  sw4

Теперь данные добавились без ошибки.

Проблема в том, что при включении PRAGMA foreign_keys строки нарушающие условие не удаляются:

sqlite> PRAGMA foreign_keys = ON;

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2
00:A9:0C:4F:55:50  10.1.3.1    10          FastEthernet0/1  sw4

Но достаточно легко найти такие строки:

sqlite> select * from dhcp where switch not in (select hostname from switches);
mac                ip          vlan        interface         switch
-----------------  ----------  ----------  ----------------  ----------
00:A9:0C:4F:55:50  10.1.3.1    10          FastEthernet0/1  sw4

А затем, если необходимо, удалить:

sqlite> delete from dhcp where switch not in (select hostname from switches);
sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2

ON DELETE, ON UPDATE

При указании внешнего ключа можно добавлять дополнительные критерии: ON DELETE и ON UPDATE. Они контролируют, что будет происходить при удалении строк из родительской таблицы и обновлении.

В каждом правиле можно указать действие, но только одно. По умолчанию, используется действие NO ACTION, то есть, ничего не выполняется.

Доступные действия:

  • SET NULL - если родительский ключ был удален (или обновлен), в дочерней таблице значение столбца, который используется как внешний ключ, будет установлено в NULL
  • SET DEFAULT - аналогично SET NULL, но вместо NULL будет использоваться значение по умолчанию, которое указано в определении таблицы
  • CASCADE - это действие удаляет строки, в которых столбец равен родительскому ключу, при удалении или обновляет соответственно, при обновлении

Для примера выбрано действие CASCADE. И определение таблиц выглядит так:

CREATE TABLE switches (
    hostname    text not null primary key,
    location    text
);

CREATE TABLE dhcp (
    mac          text primary key,
    ip           text,
    vlan         text,
    interface    text,
    switch       text,
    FOREIGN KEY (switch) REFERENCES switches (hostname)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Можно создать базу данных с нуля:

sqlite> .read dhcp_snooping_schema_ver2.sql

sqlite> .mode csv
sqlite> .import dhcp.csv dhcp
sqlite> .import switches.csv switches
sqlite> .mode column

Содержимое таблицы switches:

sqlite> select * from switches;
hostname    location
----------  -------------------------
sw1         London, 21 New Globe Walk
sw2         London, 21 New Globe Walk
sw3         London, 21 New Globe Walk

Содержимое таблицы dhcp:

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2

Не забываем включить PRAGMA foreign_keys:

sqlite> PRAGMA foreign_keys = ON;

Теперь, если мы решили изменить имя коммутатора, достаточно изменить его в таблице switches, а поле switch в dhcp изменится само:

sqlite> UPDATE switches set hostname = 'cisco-sw1' where hostname = 'sw1';

sqlite> select * from switches;
hostname    location
----------  -------------------------
cisco-sw1   London, 21 New Globe Walk
sw2         London, 21 New Globe Walk
sw3         London, 21 New Globe Walk

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:09:BB:3D:D6:58  10.1.10.2   10          FastEthernet0/1  cisco-sw1
00:04:A3:3E:5B:69  10.1.5.2    5           FastEthernet0/1  cisco-sw1
00:05:B3:7E:9B:60  10.1.5.4    5           FastEthernet0/9  cisco-sw1
00:07:BC:3F:A6:50  10.1.10.6   10          FastEthernet0/3  cisco-sw1
00:09:BC:3F:A6:50  192.168.10  1           FastEthernet0/7  cisco-sw1
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2

Но, с большей осторожностью надо относиться к установке действия ON DELETE CASCADE, так как при удалении строки из таблицы switches, будут удалены и соответствующие строки в таблице dhcp:

sqlite> DELETE from switches where hostname = 'cisco-sw1';

sqlite> select * from switches;
hostname    location
----------  -------------------------
sw2         London, 21 New Globe Walk
sw3         London, 21 New Globe Walk

sqlite> select * from dhcp;
mac                ip          vlan        interface        switch
-----------------  ----------  ----------  ---------------  ----------
00:E9:BC:3F:A6:50  100.1.1.6   3           FastEthernet0/2  sw3
00:A9:BB:3D:D6:58  10.1.10.20  10          FastEthernet0/7  sw2
00:B4:A3:3E:5B:69  10.1.5.20   5           FastEthernet0/5  sw2
00:C5:B3:7E:9B:60  10.1.5.40   5           FastEthernet0/9  sw2
00:A9:BC:3F:A6:50  10.1.10.60  20          FastEthernet0/2  sw2

Дополнительные материалы

Документация:

Leave a Comment

You May Also Enjoy

Бонус

less than 1 minute read

Первые 5 человек, которые сделают минимум заданий на сертификат, получат в подарок небольшой бонус - печатную версию книги Python tricks