§10. Внешние ключи.
Вне́шний ключ (англ. foreign key) — понятие теории реляционных баз данных.
Неформально выражаясь, внешний ключ представляет собой множество атрибутов некоторого отношения R2, значения которых должны совпадать со значениями некоторого потенциального ключа отношения R1.
Формальное определение. Допустим, что R2 —реляционное отношение (таблица). В таком случае внешним ключом FK в R2 является множество атрибутов R2 такое, что выполняются следующие требования:
существует отношение R1 (R1 и R2 не обязательно должны быть разными) с потенциальным ключом ПК;
Каждому значению FK в отношении R2 соответствует значение FK в отношении R1.
Пример. R2-таблица «Расписание занятий КФУ», R1-таблица «Преподаватели КФУ». Внешним ключом в R2 является атрибут Фамилия преподавателя. Условие 2 в определении внешнего ключа означает, что в расписании не должно быть «левых» преподавателей – каждый преподаватель должен быть предварительно описан в таблице R1.
Поддержка внешних ключей также называется соблюдением ссылочной целостности. Реляционные СУБД поддерживают автоматический контроль ссылочной целостности.
Ссылочная целостность
Определение. Связи между данными, хранимыми в разных отношениях, в реляционной БД устанавливаются с помощью использования внешних ключей — для установления связи между кортежем из отношения A с определённым кортежем отношения B в предусмотренные для этого атрибуты кортежа отношения A записывается значение первичного ключа (а в общем случае значение потенциального ключа) целевого кортежа отношения B. Таким образом, всегда имеется возможность выполнить две операции:
-определить, с каким кортежем в отношении B связан определённый кортеж отношения A;
-найти все кортежи отношения A, имеющие связи с определённым кортежем отношения B.
Благодаря наличию связей в реляционной БД можно хранить факты без избыточного дублирования, то есть в нормализованном виде. Ссылочная целостность может быть проиллюстрирована следующим образом:
Дана пара отношений A и B, связанных внешним ключом. Первичный ключ отношения B — атрибут B.key. Внешний ключ отношения A, ссылающийся на B — атрибут A.b. Ссылочная целостность для пары отношений A и B имеет место тогда, когда выполняется условие: для каждого кортежа отношения A существует соответствующий кортеж отношения B, то есть кортеж, у которого (B.key = A.b).
База данных обладает свойством ссылочной целостности, когда для любой пары связанных внешним ключом отношений в ней условие ссылочной целостности выполняется.
Если вышеприведённое условие не выполняется, говорят, что в базе данных нарушена ссылочная целостность. Такая БД не может нормально эксплуатироваться, так как в ней разорваны логические связи между зависимыми друг от друга фактами. Непосредственным результатом нарушения ссылочной целостности становится то, что корректным запросом не всегда удаётся получить корректный результат.
Так, в примере реляционная БД, состоящая из таблиц Address и Street, обеспечивает хранение адресов. При этом основная таблица, — Address, — содержит непосредственно номер дома и квартиры, а вместо имени улицы в поле Street имеет внешний ключ, ссылающийся на таблицу Street — справочник улиц. Очевидно, что полноценный адрес должен быть представлен двумя связанными записями в обеих названных таблицах, что технически выражается в условии: для любой записи таблицы Address в таблице Street должна существовать соответствующая запись, то есть запись со (Street.Key = Address.Street). Чтобы получить список полных адресов из таблиц такой структуры, когда в них соблюдается ссылочная целостность, достаточно применить к данным таблицам SQL-запрос:
select * from Address, Street where Address.Street = Street.Key;
В данном примере, однако, ссылочная целостность нарушена. Две записи таблицы Address (Key = 887 и Key = 994) имеют в поле Street так называемые «висящие» ссылки — значения, которым не соответствуют записи в таблице Street (эти ссылки показаны красным цветом). Из-за этого результат вышеприведённого запроса не будет содержать этих двух записей — для них условие запроса не выполнится. И ещё одна запись не будет выбрана вышеприведённым запросом — запись таблицы Address с (Key = 85). Это вариант намеренного (и, в некоторых случаях, легального) нарушения ссылочной целостности — в поле внешнего ключа записан NULL (показано голубым цветом). Чтобы получить список всех адресов, даже тех, у которых не указана улица, необходимо использовать открытое соединение, в одном из вариантов синтаксиса записываемое так:
select * from Address left outer join Street on (Address.Street = Street.Key)
Если же требуется получить список, не включающий записи с «висящими» ссылками, то придётся усложнить запрос:
select * from Address left outer join Street on ((Address.Street = Street.Key) or (Address.Street is null))