Создание таблиц с ограничениями ссылочной целостности
Для создания таблиц с поддержкой ограничений ссылочной целостности в SQL предназначены команды CREATE TABLE и команда ALTER TABLE. Таким образом, вы имеете два основных способа для поддержки ссылочной целостности в реляционной базе данных:
- использование предложений PRIMARY KEY и FOREIGN KEY команды CREATE TABLE;
- использование предложений ADD/DROP PRIMARY KEY и ADD/DROP FOREIGN KEY команды ALTER TABLE.
В предыдущих разделах уже было показано использование предложения PRIMARY KEY команды CREATE TABLE. Пример использования предложения FOREIGN KEY команды CREATE TABLE продемонстрируем на примере создания таблицы для иерархии "руководитель-подчиненный":
CREATE TABLE MANAGEMENT ( MANAGNO INT NOT NULL, EMPNO INT, JOB INT, PRIMARY KEY (MANAGNO), FOREIGN KEY fnkey (EMPNO) REFERENCES EMPLOYEE ON DELETE CASCADE);
CREATE UNIQUE INDEX ndxmng ON MANAGEMENT(MANAGNO);
fnkey - имя внешнего ключа, предложение REFERENCES, связанное с предложением FOREIGN KEY, определяет имя таблицы-родителя, предложение ON DELETE определяет правило удаления записей в связанных таблицах. Каждое правило удаления соответствует определенной взаимосвязи между объектами реляционной базы данных (т. е. предметной области).
Правила удаления используются только в определении внешнего ключа. Обычно СУБД в соответствии со стандартом SQL поддерживает три правила:
- CASCADE - сначала удаляется заданная строка в родительской таблице, а затем удаляются зависимые от нее строки;
- RESTRICT - строка может быть удалена, если никакие другие строки не зависят от нее, в противном случае удаления не происходит;
- SET NULL - для любого удаляемого первичного ключа соответствующее ему значение внешнего ключа дочерней строки принимает нуль-значение.
Для самоссылающихся таблиц используется только правило CASCADE. Правило RESTRICT препятствует удалению строки в таблице-родителе, если ей соответствуют какая-либо строка в таблице-потомке. Правило CASCADE определяет, что, когда строка в таблице-родителе удаляется, то все связанные с ней строки в таблице-потомке автоматически должны быть удалены. Правило SET NULL определяет, что, когда строка в родительской таблице удаляется, значения внешнего ключа во всех строках таблицы-потомка должны автоматически устанавливаться в нуль-значение.
Применение команды ALTER TABLE продемонстрируем на примере создания отношения "родитель-потомок" для таблиц DEPARTAMENT и EMPLOYEE учебной базы данных. Первичные ключи и индексы для этих таблиц уже созданы. Создадим внешние ключи (в таблицу DEPARTAMENT должна быть добавлена колонка EMPNO).
ALTER TABLE DEPARTAMENT FOREIGN KEY EMP_DEP (EMPNO) REFERENCES EMPLOYEE ON DELETE RESTRICT;
Для того, чтобы удалить первичный или внешний ключ таблицы, необходимо использовать предложение DROP команды ALTER TABLE.
