Create Foreign Key

Внешний ключ Foreign Key служит для связи родительской и дочерних таблиц в базе данных. Т.е. обеспечивает соответствие значений полей одной записи родительской таблицы множеству значений полей записей дочерних таблиц. На этом условии могут быть определены отношения сущностей в базе данных «один-к-одному» и «один-ко-многим». Внешний ключ строится по столбцам дочерней таблицы, значения которых ссылаются на значения записей в родительской таблице.

Когда одно или несколько полей одной таблицы ссылаются на соответствующее количество полей другой таблицы - то эта связь называется внешним ключом Foreign Key, а поле (поля), на которое оно ссылается, называется родительским ключом. Имена внешнего и родительского ключей не обязательно должны быть одинаковыми. Внешний и родительские ключи должны иметь одинаковые типы полей, которые располагаются в одинаковом порядке. Каждое значение поля внешнего ключа должно недвусмысленно ссылаться к соответствующему значению родительского ключа. Если это условие соблюдается, то база данных находится в состоянии ссылочной целостности, контроль которой осуществляет сервер.

Синтаксис описания внешнего ключа FOREIGN KEY

FOREIGN KEY <columns_table>
  REFERENCES <pktable> [ <columns_ptable> ] 
    [ON DELETE <rule> ] 
    [ON UPDATE <rule> ];

где

- columns_table
список столбцов таблицы, входящие во внешний ключ; символ разделения столбцов - запятая.

- pktable
таблица, содержащая родительский ключ.

- columns_ptable
список столбцов родительского ключа.

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

• иметь одинаковое число столбцов;
• типы столбцов внешнего ключа должны соответствовать типам столбцов родительского ключа.

- rule
правило, которое может определять значение полей внешнего ключа при выполнении DML транзакции в родительской таблице : [ CASCADE | RESTRICT | SET NULL | NO ACTION | SET DEFAULT ]. СУБД контролирует значения полей связанных ("дочерних") таблиц во время обновления или удаления данных в родительской таблице.

Правило CASCADE

Правило SQL CASCADE следует использовать, если необходимо в связанных таблицах выполнять обновление или удаление записей, при обновлении или удалении записей родительской таблицы. Т.е. что происходит с записью в родительской таблице, тоже самое произойдет с записью в дочерних таблицах.

Правило RESTRICT

Правило SQL RESTRICT следует использовать, если необходимо не допустить удаление записи родительской таблицы при наличии связанных записей в дочерних таблицах.

Правило SET NULL

Если при описании внешнего ключа использовано правило SET NULL, то записям дочерней таблицы будет присвоено нулевое значение при обновлении или удалении соответствующих записей родительской таблицы. Конечно, это правило будет выполняться, если поля дочерней таблицы это допускают.

Правило NO ACTION

Правило NO ACTION определяет запрет изменения/удаления записи в родительской таблице при наличии связанных записей в дочерней таблице. Если правило "ON UPDATE" или "ON DELETE" не задано явно при объявлении Foreign Key, то действует по умолчанию правило "NO ACTION".

Правило SET DEFAULT

В столбец (столбцы) внешнего ключа записей дочерней таблицы заносятся значения столбца по-умолчанию, указанное при создании таблицы (параметр DEFAULT). Если значение не определено, то возбуждается исключение.

Создать внешний ключ Foreign Key можно как вместе с таблицей, при уже созданной родительской таблицей, так и отдельно. При создании Foreign Key отдельно от таблицы необходимо использовать оператор "ALTER TABLE table_name ADD CONSTRAINT foreign_key Foreign Key ...".

Часто используют внешние ключи со ссылкой на первичные ключи, это хорошая стратегия. В этом случае внешние ключи связываются не просто с родительскими ключами, на которые они ссылаются; они связываются с определенной строкой родительской таблицы, где этот ключ располагается.

Пример создания Foreign Key

Создадим три таблицы : справочник пользователей users, справочник товаров goods и рабочую таблицу со счетами/накладными invoices. В таблице накладных будут храниться записи, ссылающиеся на идентификаторы пользователей и товара. В качестве СУБД используем MySQL.

SQL скрипты создания таблиц

-- справочник пользователей
CREATE TABLE users (
    uid       INT NOT NULL PRIMARY KEY, -- идентификатор пользователя
    name      varchar(128) NOT NULL
) ENGINE=InnoDB CHARACTER SET=UTF8;

-- справочник товаров
CREATE TABLE goods (
    gid       int NOT NULL PRIMARY KEY, -- идентификатор товара
    name      varchar(32) NOT NULL
) ENGINE=InnoDB CHARACTER SET=UTF8;

-- таблица накладных
CREATE TABLE invoices (
    iid        int NOT NULL, -- идентификатор счета/накладной
    uid        int NOT NULL,
    gid        int NOT NULL,
    quantity   decimal(9,4) NOT NULL,
    data       date NOT NULL,
  PRIMARY KEY(uid, iid)
) ENGINE=InnoDB CHARACTER SET=UTF8;

Для таблицы "invoices" создадим внешние ключи, которые обеспечат каскадное обновление записей при обновлении соответствующих записей родительской таблицы, но блокируют удаление "родительских записей". Таким образом, любые изменения в таблицах users и goods автоматически отразятся в таблице "invoices". Но если товар заказан или если у пользователя есть счет, то родительские записи не могут быть удалены.

Create Foreign Key

Внешние ключи Foreign Key можно создать с использованием следующих SQL-скриптов :

ALTER TABLE invoices
   ADD CONSTRAINT fkInvoicesUsers FOREIGN KEY (uid)
  REFERENCES test.users(uid)
     ON UPDATE CASCADE
     ON DELETE RESTRICT;

ALTER TABLE invoices
 ADD CONSTRAINT fkInvoicesGoods FOREIGN KEY (gid) 
  REFERENCES test.goods(gid)
     ON UPDATE CASCADE
     ON DELETE RESTRICT;

Также Foreign Key таблицы "invoices" можно создать изменением скрипта описания таблицы.

CREATE TABLE invoices (
    iid        int NOT NULL,  -- идентификатор счета/накладной
    uid        int NOT NULL,
    gid        int NOT NULL,
    quantity   decimal(9,4) NOT NULL,
    data       date NOT NULL,
  PRIMARY KEY(uid, iid),
  CONSTRAINT fkInvoicesUsers FOREIGN KEY (uid) 
     REFERENCES users(uid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
  CONSTRAINT fkInvoicesGoods FOREIGN KEY (gid) 
     REFERENCES goods(gid)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB CHARACTER SET=UTF8;

Попробуем добавить в таблицу накладных запись :

insert into invoices (iid, uid, gid, quantity, data) values (1, 0, 0, 23, NOW());

Сервер выдаст сообщение :

SQL2.sql: Ошибка: (1,1): Cannot add or update a child row : a foreign key constraint 
fails (`test`.`invoices`, CONSTRAINT `fkInvoicesUsers` FOREIGN KEY (`uid`) 
        REFERENCES `users` (`uid`) ON UPDATE CASCADE)

Запишем в справочные таблицы несколько тестовых записей :

-- Запишем в таблицу users несколько пользователей
insert into users (uid, name) values ( 1, 'Serg' );
insert into users (uid, name) values ( 2, 'Olga' );

-- сообщение сервера
SQL2.sql: 1 Строка вставлена [0,012c]
SQL2.sql: 1 Строка вставлена [0,010c]

-- Запишем в таблицу goods несколько товаров
insert into goods (gid, name) values ( 1, 'Кофе' );
insert into goods (gid, name) values ( 2, 'Чай' );

-- сообщение сервера
SQL2.sql: 1 Строка вставлена [0,012c]
SQL2.sql: 1 Строка вставлена [0,010c]

Теперь можно добавить строку и в таблицу накладных :

-- Добавим строку в таблицу invoices 
insert into invoices (iid, uid, gid, quantity, data) values (1, 1, 2, 23, NOW());

-- сообщение сервера
SQL2.sql: 1 Строка вставлена [0,022c]

Удаление внешнего ключа, DROP Foreign Key

Для удаления FOREIGN KEY используйте следующий SQL :

-- MySQL
ALTER TABLE table_name
  DROP FOREIGN KEY foreign_key_name;
  
-- Oracle, MSSQL, PostgreSQL
ALTER TABLE table_name
  DROP CONSTRAINT foreign_key_constraint;