5.8. Определение внешних ключей

5.8. Определение внешних ключей

Внешние ключи используются для организации связей между таблицами базы данных (родительскими и дочерними) и для поддержания ограничений ссылочной целостности данных. В СУБД SYBASE SQL Anywhere ссылочная целостность проверяется при:

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

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

Для проверки действия механизма внешних ключей перепишем содержимое таблиц Books и Copies базы данных Biblia в одноименные таблицы базы данных Dubl_Biblia. Такая возможность довольно просто реализуется в утилите ISQL. Для решения поставленной задачи необходимо в среде этой утилиты установить соединение с базой данных Biblia и произвести экспорт содержимого таблиц в текстовые файлы. Это могут сделать владельцы таблиц, пользователи, имеющие привилегию SELECT (см. табл. 7) для этих таблиц, и администратор базы данных.

Экспорт в данном случае реализуется следующими операторами:

/* Экспорт в текстовые файлы каталога 
C:\temp содержимого таблиц Books и Copies */
UNLOAD TABLE Stepanov.Books 
TO 'c:\temp\books.txt';
UNLOAD TABLE Stepanov.Copies 
TO 'c:\temp\copies.txt'

После этого следует опять же в утилите ISQL подключиться к базе данных Dubl_Biblia. При этом отсоединение от базы данных Biblia произойдет автоматически. Теперь требуется выполнить операторы, осуществляются импорт данных:

/* Импорт из текстовых файлов содержимого 
таблиц Books и Copies */
LOAD TABLE Stepanov.Copies 
FROM 'c:\temp\copies.txt';
LOAD TABLE Stepanov.Books 
FROM 'c:\temp\books.txt'

Выполнять операторы LOAD имеют право владельцы таблиц, пользователи, имеющие привилегию INSERT (см. табл. 7) для этих таблиц, и администратор базы данных.

Для проверки результатов копирования достаточно просмотреть содержимое таблиц при помощи оператора SELECT (см. табл. 7).

Внешние ключи могут создавать:

пользователи - владельцы родительской и дочерней таблиц;
пользователи с полномочиями ALTER (см. табл. 7) для дочерней и REFERENCES (см. табл. 7) - для родительской таблиц;
администратор базы данных.

В утилите SQL Central внешние ключи создаются мастером Add Foreign Key. Для доступа к нему следует последовательно раскрыть папки Tables - <имя таблицы> - Foreign Keys. Мастер Add Foreign Key выполняет ряд шагов, которые мы рассмотрим по устоявшейся схеме (п.5.1). Работу мастера покажем на примере создания внешнего ключа таблицы Copies, который ссылается на таблицу Books.

Шаг 1. Начальная стадия создания внешнего ключа таблицы данных (рис. 63).

Рис. 63. Создание внешнего ключа таблицы данных

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

Внешние ключи, создаваемые пользователями СУБД SQL Anywhere, обеспечивают поддержание ссылочной целостности баз данных.

Дочерняя таблица: Copies.

Вопрос. На какую таблицу будет ссылаться создаваемый внешний ключ?

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

После выбора родительской таблицы следует щелкнуть по кнопке Далее для выполнения следующего шага.

Шаг 2. Выбор полей родительской таблицы, на которые будет ссылаться внешний ключ дочерней таблицы данных (рис. 64).

Рис. 64. Выбор полей родительской таблицы, на которые будет ссылаться внешний ключ дочерней таблицы данных

Вопрос 1. Создаваемый внешний ключ будет указывать на первичный ключ или совокупность полей с признаком уникальности?

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

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

Вопрос 2. Какие поля внешнего ключа будут соответствовать полям первичного ключа родительской таблицы?

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

Если предлагаемое соответствие вас устраивает, то переходим к очередному шагу.

Шаг 3. Определение названия и комментария к внешнему ключу (рис. 65).

Рис. 65. Определение названия и комментария к внешнему ключу

Вопрос. Какое название будет иметь новый внешний ключ?

Следующее ниже поле окна представляет собой средство для ответа на поставленный вопрос. Назовем внешний ключ Books_Copies.

Пояснение. Вы можете включить комментарий для нового внешнего ключа.

Ниже располагается поле для ввода комментария для создаваемого внешнего ключа.

Произведя все манипуляции, приступаем к следующему шагу.

Шаг 4. Задание атрибутов внешнего ключа (рис. 66).

Рис. 66. Задание атрибутов внешнего ключа

Пояснение 1. SQL Anywhere позволяет внешнему ключу содержать значения NULL.

Вопрос 1. Желаете ли вы разрешить хранение значений NULL в полях, составляющих внешний ключ?

Для ответа на этот вопрос предназначен флажок Allows NULLS. В тех случаях, когда это недопустимо, например как в нашем, ниже в области Note приводится комментарий по этому поводу.

Комментарий. Ни одно из полей, составляющих внешний ключ, не допускают хранение значений NULL.

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

Вопрос 2. Желаете ли вы производить проверку целостности только при выполнении оператора COMMIT?

Флажок Check on COMMIT позволяет ответить на этот вопрос.

Оператор COMMIT осуществляет завершение текущей и начало следующей транзакций. В некоторых случаях необходимо при выполнения последовательности SQL-операторов, изменяющих сразу несколько таблиц, выполнять проверку целостности только при завершении транзакций. SQL Anywhere предоставляет такую возможность и именно этим вызван вопрос 2.

В нашем случае нет необходимости откладывать проверку ограничений целостности и поэтому оставим флажок Check on COMMIT в сброшенном состоянии.

После выполнения действий, предписанных в шаге 4, переходим шагу 5.

Шаг 5. Определение условий ссылочной целостности (рис. 67).

Рис. 67. Определение условий ссылочной целостности

Вопрос 1. Какие действия должны предприниматься для поддержания ссылочной целостности?

Для ответа на этот вопрос используются переключатели Update Action и Delete Action. Первый из них, Update Action, определяет действие, выполняемое при модификации значений полей родительской таблицы, на которые ссылаются поля внешнего ключа дочерней таблицы. Назовем эти поля родительской таблицы адресуемыми полями. Второй переключатель Delete Action предназначен для выбора действия, выполняемого при удалении записей родительской таблицы.

Положения переключателя Update Action. регламентируют выполнение следующих действий при модификации адресуемых полей родительской таблицы:

Restrict Update - запрещение модификацию адресуемых полей;
Cascade - (каскадное обновление) изменение значений полей внешнего ключа в записях, ссылающихся на модифицируемую запись родительской таблицы, на новое значение адресуемых полей;
Set NULL- изменение значений полей внешнего ключа в записях, ссылающихся на модифицируемую запись родительской таблицы, на значение NULL;
Set Default - изменение значений полей внешнего ключа в записях, ссылающихся на модифицируемую запись родительской таблицы, на значение присваиваемое полю по умолчанию.

Для указания действий, которые надлежит выполнить при удалении записей родительской таблицы используется переключатель Delete Action. Он следующие положения:

Restrict Delete- запрещение удаления;
Cascade - (каскадное удаление) удаление записей дочерней таблицы, ссылающихся на удаляемую запись родительской таблицы;
Set NULL- изменение значений полей внешнего ключа в записях, ссылающихся на удаляемую запись родительской таблицы, на значение NULL;
Set Default - изменение значений полей внешнего ключа в записях, ссылающихся на удаляемую запись родительской таблицы, на значение присваиваемое полю по умолчанию.

Как показано на рис. 67 для создаваемого внешнего ключа определено каскадное обновление полей внешнего ключа дочерней таблицы (поле Copies.Code_book) при изменение адресуемых полей родительской таблицы (поле Books.Code_book) - (Update Action/ Cascade) и запрещение удаления записей родительской таблицы Books, на которую имеются ссылки из дочерей таблицы Copies. (Delete Action / Restrict Delete).

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

Шаг 6. Подтверждение готовности к созданию нового внешнего ключа (рис. 68).

Рис. 68. Подтверждение готовности к созданию нового внешнего ключа

Пояснение 1. Информация о дочерней таблице.

Вы подготовили данные для создания внешнего ключа:
Books_Copies

на базе поля:
Code_book.

Пояснение 2. Информация о родительской таблице.

Родительская таблица:
Books

адресуемым поле:
Code_book.

Щелкните мышью по кнопке Готово для создания внешнего ключа.

Целью шагов 1-6 мастера Add Foreign Key является подготовка к выполнению операторов ALTER TABLE. После щелчка мышью по кнопке Aioiai (шаг 6) начнется выполнение этого оператора. Его результатом будет появление нового внешнего ключа Books_Copies. Данный факт выражается в появлении одноименного элемента в папке Foreign Keys таблицы Copies (см. рис. 69).

Рис. 69. Обновленное содержание папки Foreign Keys

Кроме того в папке Referenced By таблицы Books отображается наличие через внешний ключ ссылку на эту таблицу из таблицы Copies.

Для создания в утилите ISQL внешнего ключа Books_Copies требуется выполнить следующие SQL-операторы:

//Создание внешнего ключа
ALTER TABLE Stepanov.Copies 
ADD "Books_Copies " NOT NULL 
FOREIGN KEY(Code_book) //внешний ключ
 			//состоит из поля Code_book
REFERENCES /* он ссылается на поле
 		Code_book таблицы Stepanov.Books */
 	Stepanov.Books(Code_book)
ON UPDATE CASCADE /* каскадное 
 	обновление полей внешнего ключа дочерней 
	таблицы при изменение адресуемых полей 
	родительской таблицы */
ON DELETE RESTRICT /* запрещение 
 	удаления записей родительской таблицы, 
 	на которую имеются ссылки из дочерей 
 	таблицы */
// CHECK ON COMMIT /* признак проверки 
 		целостности только при выполнении 
 		оператора COMMIT */
COMMENT // комментарий к внешнему ключу
ON FOREIGN KEY 
Stepanov.Copies.Books_Copies IS
'Внешний ключ для поддержания 
ограничений ссылочной целостности
между таблицами Copies и Books'

Для того, чтобы убедиться в утилите ISQL, в том что внешний ключ Books_Copies действительно был создан необходимо при помощи оператора SELECT проанализировать системное представление SYS.SYSFOREIGNKEYS.

Рассмотрим на примерах действие внешнего ключа Books_Copies по обеспечению ссылочной целостности. Для этого приведем по одному фрагменту из таблиц Copies и Books (табл. 15, 16).

Таблица 15. Фрагмент исходной таблицы Books

Code_book Author ....... Number

1 Коршунов Ю.М. ....... 49

2 Лавров И.А. ....... 2

3 Трауб Дж ....... 38

... ....... ....... .......

Таблица 16. Фрагмент исходной таблицы Copies

N_ books Code_book Present

....... ....... .......

49 1 1

50 2 1

51 2 1

52 3 1

....... ....... .......

Поля таблицы Books, представленные в табл. 15, служат для хранения уникального кода книги, фамилии ее автора, еще ряда характеристик и количества ее экземпляров соответственно. В таблице Copies для каждой книги (поле Code_book) представлены номера ее экземпляров (поле N_ book) и признак наличия данного экземпляра в библиотеке, т.е. признак того, его еще не потеряли или не списали (поле Present).

Примеры будем выполнять в утилите ISQL. Перед их выполнением убедимся в том, что в данной утилите установлен режим завершения транзакции при окончании работы с утилитой (см. рис.24). Это позволит проводить все действия с базой данных в виртуальной памяти сервера , не изменяя содержимого базы данных на внешнем носителе. При этом имеется возможность отменить все изменения, проводимые в виртуальной памяти.

Проведем модификацию значения поля Code_book - ключевого поля таблицы Books. Для этого выполним следующий SQL-оператор:

//Изменение значения поля
UPDATE Stepanov.Books
SET Code_book = 60
WHERE Code_book = 2

Измененное содержимое таблиц Copies и Books представлено в табл. 17, 18.

Таблица 17. Фрагмент таблицы Books после после выполнения оператора UPDATE

Code_book Author ....... Number

1 Коршунов Ю.М. ....... 49

60 Лавров И.А. ....... 2

3 Трауб Дж ....... 38

....... ....... ....... .......

Таблица 18. Фрагмент таблицы Copies выполнения оператора UPDATE

N_ books Code_book Present

....... ....... .......

49 1 1

50 60 1

51 60 1

52 3 1

....... ....... .......

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

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

Теперь попробуем удалить запись родительской таблицы, на которую имеются ссылки из дочерней таблицы - запись, у которой поле Code_book=2. Для этого выполним оператор вида:

// Удаление записи таблицы
DELETE FROM Stepanov.Books
WHERE Code_book=2

Поскольку для внешнего ключа Code_book такие действия запрещены, пользователю будет выдано сообщение "primary key for row in table 'Copies' is referenced in another table". Оно является сообщением об аварийном завершении оператора и означает, что на первичный ключ текущей таблицы (в нашем случае таблица Stepanov.Books) имеется ссылка из таблицы Copies.

Проверим действие внешнего ключа для таких ограничений ссылочной целостности как Update Action/Restrict Update и Delete Action/ Cascade (см. рис. 67). Установить эти ограничения можно следующим образом. Щелкните правой кнопки мыши по пиктограмме внешнего ключа Code_book (см. рис. 69). В появившемся в результате этого контекстном меню выберите пункт Properties. В открывшемся окне свойств внешнего ключа выберите закладку Integrity. Она в имеет такой же вид как и окно шага 5 мастера Add Foreign Key (см. рис. 67). Манипулирование переключателями Update Action и Delete Action данной закладки позволяют решить поставленную задачу.

Для достижения того же эффекта в ISQL необходимо выполнить следующую последовательность SQL-оператор:

/* Удаление существующего 
внешнего ключа таблицы данных*/
ALTER TABLE Stepanov.Copies 
DELETE FOREIGN KEY Books_Copies;
/* Формирование нового внешнего
 ключа таблицы данных*/
ALTER TABLE Stepanov.Copies 
ADD Books_Copies NOT NULL
FOREIGN 	KEY(Code_book) // в состав 
 	// ключа входит только поле Code_book 
REFERENCES // ключ ссылается на поле 
Stepanov.Books(Code_book) //Code_book
			//таблицы Stepanov.Books */
ON UPDATE RESTRICT /* запрещение
	модификации адресуемых 
	полей родительской таблицы */
ON DELETE CASCADE; /* каскадное
	удаление записей таблицы дочерней
	таблицы, ссылающихся на удаляемую
	запись родительской таблицы */

Выполним те же SQL-операторы UPDATE и DELETE уже при этих ограничениях целостности. При этом выполнение оператора UPDATE будет запрещено. Результатом работы оператора DELETE будет изменение таблиц Books и Copies, представленное в табл. 19, 20.

Таблица 19. Фрагмент таблицы Books после выполнения оператора DELETE

Code_book Author ....... Number

1 Коршунов Ю.М. ....... 49

3 Трауб Дж ....... 38

....... ....... ....... .......

Таблица 20. Фрагмент таблицы Copies после выполнения оператора DELETE

N_ books Code_book Present

....... ....... .......

49 1 1

52 3 1

....... ....... .......

Анализ табл. 19, 20 показал, что синхронное удаление записей обеих таблиц происходит таким образом, чтобы не было "висячих" ссылок. Теперь вернем внешний ключ в предыдущее состояние.

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

Назад | Содержание | Вперед