Ссылочная целостность: внешний ключ (FOREIGN KEY)
Внешний ключ – это ограничение, которое поддерживает согласованное состояние данных между двумя таблицами, обеспечивая так называемую ссылочную целостность. Этот тип целостности означает, что всегда есть возможность получить полную информацию об объекте, распределенную по нескольким таблицам. Причины такого распределения, связанные с принципами проектирования реляционной модели, мы рассмотрим в дальнейшем.
Связь между таблицами не является равноправной. В ней всегда есть главная таблица и таблица подчиненная. Связи бывают двух типов: «один к одному» и «один ко многим». Связь «один к одному» означает, что строке главной таблицы соответствует не более одной строки (т.е. одна или ни одной) в подчиненной таблице. Связь «один ко многим» означает, что одной строке главной таблицы отвечает любое число строк (в том числе и 0) в подчиненной таблице.
Связь устанавливается посредством равенства значений определенных столбцов в главной и подчиненной таблицах. При этом столбец (или набор столбцов в случае составного ключа) в подчиненной таблице, который соотносится со столбцом (или набором столбцов) в главной таблице, и называется внешним ключом.
Поскольку главная таблица всегда находится со стороны «один», то столбец, участвующий в связи по внешнему ключу, должен иметь ограничение PRIMARY KEY или UNIQUE. Внешний же ключ задается при создании или изменении структуры подчиненной таблицы при помощи спецификации FOREIGN KEY:
FOREIGN KEY(<список столбцов 1> REFERENCES <имя главной таблицы>(<список столбцов 2>)Количество столбцов в списках 1 и 2 должно быть одинаковым, а типы данных этих столбцов должны быть попарно совместимы.
Вот как можно создать внешний ключ в таблице PC:
ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product(model);Замечание. Для главной таблицы можно не указывать столбец в скобках, если он является первичным ключом, т.к. он может быть только один. В нашем случае так и есть, поэтому последнюю строку можно было написать в виде
FOREIGN KEY(model) REFERENCES Product;Аналогичным образом создаются внешние ключи в таблицах Printer и Laptop.
Теперь пора разобраться с тем, как работает ограничение внешнего ключа.
Поскольку это ограничение поддерживает согласованность данных в двух таблицах, оно препятствует возникновению таких строк в подчиненной таблице, для которых нет соответствующих строк в главной таблице. Рассогласование могло бы возникнуть в результате выполнения следующих действий.
1. Добавление в подчиненную таблицу строки, для которой нет соответствия в главной таблице. В нашем случае внешние ключи не позволят добавить ни в одну из продукционных таблиц (PC, Laptop или Printer) изделия, модели которого нет в таблице Product. Например, мы получим ошибку при попытке выполнить такой оператор:
insert into pc values(13, 1126, 500, 64, 10, '24x', 650);т.к. модели 1126 нет в таблице Product.
(Конфликт инструкции INSERT с ограничением FOREIGN KEY " fk_pc_product “. Конфликт произошел в базе данных “learn”, таблица “dbo.product”, столбец ‘model’. Выполнение данной инструкции было прервано.)
2. Изменение существующего значения внешнего ключа на значение, которого нет в соответствующем столбце главной таблицы. В нашем примере ограничение не позволит выполнить такой оператор UPDATE
update pc set model = 1126 where model = 1121;и вернёт аналогичную ошибку.
3. Удаление строки из главной таблицы, для которой есть связанные строки в подчиненной таблице. Согласованность данных здесь может поддерживаться разными способами, в соответствии со значением опции в необязательном предложении
ON DELETE <опция>Возможны следующие значения опции:
- CASCADE каскадное удаление, т.е. при удалении строки из главной таблицы будут удалены также связанные строки из подчиненной таблицы. Например, при удалении модели 1121 из таблицы Product будут удалены строки с кодами 2, 4 и 5 из таблицы PC;
- SET NULL - при удалении строки из главной таблицы значение внешнего ключа становится неопределенным для тех строк из подчиненной таблицы, которые связаны с удаляемой строкой. Естественно, этот вариант подразумевает, что на внешнем ключе нет ограничения NOT NULL. В нашем примере с удалением модели 1121 из таблицы Product значение столбца model в таблице PC примет значение NULL для строк с кодами 2, 4 и 5;
- SET DEFAULT – действие аналогичное предыдущему варианту, только вместо NULL будет использовано значение по умолчанию;
- NO ACTION (принимается по умолчанию) – операция выполнена не будет, если для удаляемой строки существуют связанные строки в подчиненной таблице. Если связанных строк нет, то удаление будет выполнено.
Поскольку при создании внешнего ключа для таблицы PC мы не указали никакой опции, то будет использоваться NO ACTION – опция, принимаемая по умолчанию. Чтобы изменить поведение, скажем, на каскадное удаление, мы должны переписать ограничение внешнего ключа. Сделать это можно следующим образом:
- удалить существующее ограничение;
- создать новое ограничение.
Для удаления ограничения также используется оператор ALTER TABLE:
ALTER TABLE <имя таблицы>
DROP CONSTRAINT <имя ограничения>;Вот где нам понадобилось имя ограничения! Давайте удалим внешний ключ из таблицы PC.
ALTER TABLE PC
DROP CONSTRAINT fk_pc_product;Замечание
При удалении внешнего ключа сами столбцы не удаляются, удаляется лишь ограничение. Это также справедливо и для других ограничений.
Создадим теперь новое ограничение, использующее каскадное удаление:
ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product ON DELETE CASCADE;4. Изменение значений столбцов в главной таблице, с которыми связан внешний ключ в подчиненной таблице, т.е. тех столбцов, которые указаны в предложении REFERENCES ограничения FOREIGN KEY. Здесь действуют те же варианты, что и в случае с удалением строки из главной таблицы, только опция вводится предложением
ON UPDATE <опция>При помощи внешнего ключа, как и других ограничений, мы моделируем связи, которые существуют в предметной области. Поэтому выбор опций определяется именно предметной областью. В нашем случае при изменении номера модели в таблице Product естественно создать ограничение с опцией CASCADE, чтобы это изменение проникало в продукционные таблицы, удаляя изделия аннулированной модели, т.е. для таблицы PC нам следует написать:
ALTER TABLE PC
ADD CONSTRAINT fk_pc_product
FOREIGN KEY(model) REFERENCES Product
ON DELETE CASCADE
ON UPDATE CASCADE;Однако для другой предметной области каскадное удаление может привести к ошибочной потере данных. Пусть, например, для таблиц Сотрудники и Отделы существует связь по номеру отдела. Если при удалении (расформировании) отдела сотрудники не увольняются, а переводятся в другие отделы, то каскадное удаление ошибочно привело бы к удалению информации о сотрудниках, работавших в этом отделе. Здесь подошел бы вариант NO ACTION – чтобы сначала распределить сотрудников по другим отделам, а потом удалить «пустой» отдел; или вариант SET NULL, т.е. сначала удаляем отдел, а потом занимаемся трудоустройством сотрудников, не приписанных ни к какому отделу. Еще раз повторю, что выбор варианта зависит не от предпочтений программиста, а от процессов, имеющих место в реальном мире.
Замечания
1. Между таблицами Product и PC выше мы реализовали связь «один ко многим». Связь «один к одному» создается в случае, когда в подчиненной таблице внешним ключом является уникальный столбец или уникальная комбинация столбцов. В ряде случаев связь «один к одному» является ошибкой проектирования, поскольку фактически одна сущность разбивается на две. Однако для такого разделения иногда имеются веские основания, например, когда с целью повышения производительности или обеспечения безопасности приходится выполнить вертикальное секционирование (partitioning) таблицы.
2. При удалении ограничения необходимо знать его имя. Однако, как мы уже знаем, можно создать ограничение, не давая ему имени. Как быть в этом случае? Если мы явно не указываем имя ограничения, его генерирует система. Поэтому имя всегда есть. Другой вопрос, что мы его не знаем. Тут уместно сказать, что в реляционных системах метаданные хранятся так же, как и данные, т.е. в таблицах. Стандартным представлением метаданных является информационная схема, к которой можно адресовать обычные запросы на выборку. Не углубляясь в детали, напишем запрос, который вернет нам имя ограничения внешнего ключа для таблицы PC:
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME ='PC' AND CONSTRAINT_TYPE ='FOREIGN KEY';