Контроль целостности данных с использованием триггеров

Справочная целостность -это связь, которая позволяет избежать избыточности информации в таблицах.

Когда одно поле ссылается на другое, оно называется внешнимключом. Имена внешнего ключа и родительского ключа не обязательно должны быть одинаковыми, однако соблюдение условия идентичности имен делает соединение более понятным.

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

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

SELECT STUDENTS.SFAM, STUDENTS.SIMA, STUDENTS.SOTCH, USP.OCENKA

FROM STUDENTS, USP

WHERE STUDENTS.SNUM=USP.SNUM

AND STUDENTS.SNUM=3412;

будет неясно, какому именно студенту эта оценка принадлежит. С другой стороны, если в таблице успеваемости USP появится запись со значением поля SNUM=3417, которое отсутствует в таблице STUDENT, то в результате запроса

SELECT STUDENTS.SFAM, STUDENTS. SIMA, STUDENTS.SOTCH, USP.OCENKA

FROM USP, STUDENTS

WHERE USP.SNUM=STUDENTS.SNUM

AND USP.SNUM = 3417;

Можно получить список оценок, которые не принадлежат ни одному студенту. Очевидно, что такие ситуации будут исключены, если каждое значение во внешнем ключе будет представлено только один раз в родительском ключе, и это значение должно существовать.

SQL автоматически поддерживает справочную целостность данных с ограничением FOREIGN KEY. Эта функция ограничивает значения, которые можно вывести в БД так. Чтобы заставить внешний ключ и родительский ключ соответствовать принципу справочной целостности.

FOREIGN KEY также воздействует на возможность пользователя изменять или удалять значения родительского ключа.

Ограничение FOREIGN KEY используется в командах CREATE TABLE или ALTER TABLE, которые объявляют внешним ключом соответствующее поле таблицы, при этом дается имя родительскому ключу, на которое предусматривается ссылка внутри этого ограничителя. FOREIGN KEY может быть ограничителем поля или таблицы, причем в последнем случае- позволяет использовать несколько полей в качестве одного внешнего ключа.

Синтаксис ограничения таблицы FOREIGN KEY:

FOREIGN KEY <список полей> REFERENCES <PKTABLE> [<список полей>]

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

Второй список полей-это поля, которые будут составлять родительский ключ.

§ Списки должны содержать одинаковое количество полей;

§ В той последовательности, в которой идет перечисление, должна соблюдаться идентичность типа данных и размер каждого поля.

Создадим таблицу успеваемости USP с полем SNUM, определенным в качестве внешнего ключа, ссылающегося на таблицу студентов STUDENTS.

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGERNOT NULL,

FOREIGN KEY (SNUM)

REFERENCES STUDENTS(SNUM);

При использовании ALTER TABLE вместо CREATE TABLE для реализации ограничения FOREIGN KEY значения, которые указываются во внешнем и родительском ключах, должны быть в состоянии справочной целостности. В противном случае команда будет отклонена. Отсюда следует, что употребление команды ALTER TABLE приводит к необходимости каждый раз следить за соблюдением структурных принципов справочной целостности.

Вариант ограничения поля FOREIGN KEY часто называют ссылочным ограничением REFERENCES, поскольку оно фактически не содержит в себе ключевых слов FOREIGN KEY. А просто использует слово REFERENCES,как это показано в примере:

СREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

REFERENCES STUDENTS(SNUM),

PNUM INTEGER NOT NULL);

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

Например, если в таблице STUDENTS поле SNUM является PRIMARY KEY, то его можно использовать как во внешний ключ в таблице успеваемости аналогично предыдущему примеру следующим образом:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS,

PNUM INTEGER NOT NULL);

Родительский ключ должен быть уникальным и не содержать NULL значений. С учетом этого, при объявлении FOREIGN KEY, SQL должен быть уверенным, что двойные или NULL значения не присутствуют в родительском ключе. Пользователь должен убедиться в том, что все поля, которые используются в качестве родительских ключей, имеют ограничение PRIMARY KEY или UNIQUE и NOT NULL.

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

Для полей, определенных как внешние ключи, любые значения, помещаемые командами INSERT или URDATE, должны быть уже представлены в их родительских ключах. Более того, допускается вставлять в эти поля NULL значения, несмотря на то, что они не разрешены в родительских ключах.

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

В соответствии со стандартом, изменение или удаление значений родительского ключа вообще не допускаются. Это, например, означает, что нельзя удалить данные о студенте из таблицы STUDENTS до тех пор, пока в таблице успеваемости USP для него имеется какая-либо информация. Поведение системы во время изменения родительского ключа может отличаться от предусмотренного стандартом. Если необходимо изменить или удалить ссылочное значение родительского ключа, имеются три возможности:

-запретить изменения, оговорив, что изменения в родительском ключе ограничены;

-сделав изменения в родительском ключе, произвести изменения и во внешнем ключе автоматически, что является каскадным изменением;

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

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

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS,

PNUM INTEGER NOT NULL,

UPDATE OF STUDENTS CASCADES,

DELETE OF STUDENTS RESTRICTED);

После этого, при удалении данных о студенте из таблицы STUDENTS, команда не будет выполнена до тех пор, пока не будут удалены его оценки или не изменится значение поля SNUM. С другой стороны, если номер студенческого билета будет изменен, то для соответствующих оценок этого студента значение поля SNUM также автоматически поменяется.

Для демонстрации NULL изменений воспользуемся таким примером:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL REFERENCES STUDENTS,

PNUM INTEGER NOT NULL,

DELETE OF STUDENTS NULLS);

В этом случае удаление информации о студенте из таблицы STUDENTS пройдет успешно. При использовании внешних ключей возможны ссылки на ту же таблицу. Создадим таблицу STUDENTS2.

STUDENTS2
SNUM	SFAM	SIMA	SOTCH	STAR
	Поляков	Анатолий	Алексеевич
	Старова	Любовь	Михайловна
	Гриценко	Владимир	Николаевич
	Котенко	Анатолий	Николаевич
	Нагорный	Евгений	Васильевич	NULL

Поле STAR содержит номер студенческого билета старосты. В нашем случае студент Нагорный является старостой всего потока, ему подчиняется староста Гриценко, которому подчиняются все остальные студенты. Так как староста сам является студентом, то он тоже представлен в таблице.

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

CREATE TABLE STUDENTS2

(UNUM INTEGER NOT NULL PRIMARY KEY,

SFAM CHAR (20) NOT NULL,

SIMA CHAR (10),

SOTCH CHAR (15)

STAR INTEGER NULL REFRENCES STUDENTS2);

Таким образом каждый студент ссылается на другого как на своего старосту, однако, Нагорный, как самый старший, должен иметь в поле STAR NULL значение, т.е. это поле должно допускать неопределенные значения. Это вынуждает сделать принцип справочной целостности, иначе в такую таблицу нельзя было бы добавить ни одной записи.