Создание объектов для хранения данных. Работа с ограничениями

Отношение "родитель-потомок" между таблицами

Первичный и соответствующий ему внешний ключ позволяют реализовать отношение "родитель-потомок" (parent/child relationship) между таблицами. Они отражают взаимосвязь между объектами предметной области (представленными кортежами таблиц) через значения некоторых их атрибутов по принципу иерархического подчинения, когда объект-родитель определяет существование объектов-потомков. Сами объекты-потомки могут также выступать в качестве родителей для других объектов (descendents).

Таблица реляционной базы данных, содержащая первичный ключ, называется таблицей-родителем (parent table) или родительской таблицей, а таблица, содержащая соответствующий первичному ключу внешний ключ, - таблицей-потомком (child table) или дочерней таблицей. Таблица DEPARTAMENT учебной базы данных является таблицей-родителем для таблицы EMPLOYEE.

Таблица базы данных может не иметь ни родителей, ни потомков. Такие таблицы называются независимыми таблицами (independent tables). Они не удовлетворяют никаким ограничениям ссылочной целостности и СУБД не контролирует и не проверяет правильность ссылок к таким таблицам.

Отношение "родитель-потомок" между таблицами реализуется через атрибуты-ключи соответствующих строк. Строка, принадлежащая таблице-родителю, называется родительской строкой, а строка в таблице-потомке, на которую ссылается родительская строка, называется строкой-потомком или дочерней строкой. Строка-потомок должна иметь по крайней мере один ненулевой атрибут внешнего ключа.

Совсем необязательно, чтобы каждая строка таблицы-родителя была родительской строкой. Аналогично, если строка в таблице-потомке имеет нуль-значение внешнего ключа, то она не будет строкой-потомком.

Отношение "родитель-потомок" между двумя таблицами отражает взаимосвязь по включению на доменах соответствующих атрибутов. Однако, таблица может реализовать отношение иерархического подчинения в самой себе. Примером такой таблицы может стать виртуальная таблица из этой лекции, реализующая отношение Руководитель-подчиненный, если ее сделать таблицей базы данных. Такие таблицы называются самоссылающимися таблицами (self-referencing tables). Они одновременно содержат и первичный, и внешний ключи. Обычно на манипулирование данными в таких таблицах накладываются дополнительные ограничения.

Таблицы называются связанными по удалению (delete-connected tables), если удаление строки в одной таблице влияет на состояние строк в другой таблице. Так, если в таблице DEPARTAMENT удалить отдел (в связи с его ликвидацией), то в таблице EMPLOYEE необходимо либо удалить некоторых служащих этого отдела (уволились), либо изменить у них номер отдела (переведены в другой). Любая таблица, которая участвует в операции удаления при ограничениях ссылочной целостности, является связанной по удалению.

Также ясно, что самоссылающаяся таблица является связанной по удалению сама с собой, таблица-потомок связана по удалению со своей таблицей-родителем, таблицы-потомки в иерархии отношения "родитель-потомок" связаны по удалению в своей цепочке подчинения (правило CASCADE см. ниже).

Создание таблиц с ограничениями ссылочной целостности

Для создания таблиц с поддержкой ограничений ссылочной целостности в 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.

Особенности манипулирования данными при ограничениях ссылочной целостности

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

Для команды INSERT предусмотрены следующие правила:

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

Для команды UPDATE предусмотрены следующие правила:

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

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

Правила для команды DELETE сообщают СУБД, что ей делать, когда пользователь пытается удалить строку в таблице-родителе. Отметим также, что в командах удаления нельзя использовать подзапрос, ссылающийся на заданную в команде таблицу.

Применение команды DROP в условиях ограничений ссылочной целостности также имеет свои особенности. Так, когда вы удаляете таблицу-родитель или первичный ключ, ограничения ссылочной целостности разрушаются. Если вы удаляете первичный индекс, то вы не разрушаете ограничений ссылочной целостности, но таблица становится незавершенной. Удаление первичного или внешнего ключа физически не удаляет соответствующие колонки.