1.5. Ссылочная целостность и каскадные воздействия
Рассмотрим наиболее часто встречающуюся в базах данных связь "один-ко-многим" (рис. 1.12). Как можно заметить, дочерняя и родительская таблицы связаны между собой по общему полю "Товар". Назовем это поле полем связи.
Таблица "Товары" Таблица "Отпуск товаров"
Товар
Ед.изм.
Цена ед.
Товар
Дата
Кол-во, ед.
Сахар
кг
5000
-|->
Сахар
10.01.97
100
Макароны
кг
7000
|->
Сахар
12.01.97
200
Куры
кг
10000
|->
Сахар
14.01.97
50
Фанта
бут. 1 л
6000
Макароны
10.01.97
1000
Макароны
11.01.97
500
Фанта
10.01.97
2000
Фанта
12.01.97
3000
Рис. 1.12. Связанные таблицы базы данных
Возможны два вида изменений, которые приведут к утере связей между записями в родительской и дочерней таблицах:
• изменение значения поля связи в записи родительской таблицы без изменения значений полей связи в соответствующих записях дочерней таблицы;
• изменение значения поля связи в одной из записей дочерней таблицы без соответствующего изменения значения полей связи в родительской и дочерней таблицах.
Разберем первый случай. На рис. 1.13 показано изменение значения поля "Товар" с "Сахар" на "Рафинад" в таблице "Товары". В таблице "Отпуск товаров" значение поля связи "Сахар" осталось прежним. В результате:
в дочерней таблице "Отпуск товаров" для товара "Рафинад" (таблица "Товары") нет сведений о его отпуске со склада;
• некоторые записи таблицы "Отпуск товаров" содержат сведения об отпуске товара ("Сахар"), о котором нет информации в таблице "Товары".
|
Товар |
Ед.изм. |
Цена eд |
|
Товар |
Дата |
Кол-во, ед. |
|
Рафинад |
кг |
5000 --- |
-|-> |
Сахар |
10.01.97 |
100 |
|
Макароны |
кг |
7000 |
|-> |
Сахар |
12.01.97 |
200 |
|
Куры |
кг |
10000 |
|-> |
Сахар |
14.01.97 |
50 |
|
Фанта |
бут.1 л |
6000 |
|
Макароны |
10.01.97 |
1000 |
|
|
|
|
|
Макароны |
11.01.97 |
500 |
|
|
|
|
|
Фанта |
10.01.97 |
2000 |
|
|
|
|
|
Фанта |
12.01.97 |
3000 |
Таблица "Товары" Таблица "Отпуск товаров"
Рис. 1.13. Нарушение целостности базы данных - записи с товаром "Сахар" (таблица "Отпуск товаров") не имеют родительской записи
Разберем второй случай. Пусть в одной из записей таблицы "Отпуск товаров" значение поля связи "Сахар" изменилось на "Рафинад" (рис. 1.14). В результате:
• в дочерней таблице "Отпуск товаров" недостоверны сведения об отпуске со склада товара "Сахар" (таблица "Товары");
• одна из записей таблицы "Отпуск товаров" содержит данные об отпуске товара ("Рафинад"), сведения о котором (такие, как единица измерения и цена за единицу) отсутствуют в таблице "Товары". И в первом, и втором случаях мы наблюдаем нарушение целостности базы данных, поскольку информация в ней становится недостоверной. Следовательно, нужно блокировать действия, которые нарушают целостность связей между таблицами, которую называют ссылочной целостностью. Когда говорят о ссылочной целостности, имеют в виду совокупность связей между отдельными таблицами во всей БД. Нарушение хотя бы одной такой связи делает информацию в БД недостоверной.
Таблица "Товары" Таблица "Отпуск товаров"
Товар
ед.изм.
Цена ед.
Товар
Дата
Кол-во,ед.
Сахар
кг
5000 ---
-|->
Рафинад
10.01.97
100
Макароны
кг
7000
|->
Сахар
12.01.97
200
Куры
кг
10000
|->
Сахар
14.01.97
50
Фанта
бут.1 л
6000
Макароны
10.01.97
1000
Макароны
11.01.97
500
Фанта
10.01.97
2000
Фанта
12.01.97
3000
Рис. 1.14. Нарушение целостности базы данных - запись с товаром "Рафинад" (таблица "Отпуск товаров ") не имеет родительской записи
Чтобы предотвратить потерю ссылочной целостности, используется механизм каскадных изменений. Он состоит в обеспечении следующих требовании:
• необходимо запретить изменение поля связи в записи дочерней таблицы без синхронного изменения полей связи в родительской и дочерней таблицах; обычно инициатива изменения поля связи реализуется в записи родительской таблицы;
• при изменении поля связи в записи родительской таблице, следует синхронно изменить значения полей связи в соответствующих записях дочерней таблицы;
• при удалении записи в родительской таблице, следует удалить соответствующие записи в дочерней таблице.
Данные изменения или удаления в записях дочерней таблицы при изменении (удалении) записи родительской таблицы называются каскадными изменениями и каскадными удалениями.
Замечание 1. Существует другая разновидность каскадного удаления: при удалении родительской записи в записях дочерних таблиц значения полей связи обнуляются. Эта разновидность применяется редко.
Замечание
2. Обычно занесение записей в дочернюю таблицу осуществляется так: выбирается значение родительской записи (например, из выпадающего списка), значение поля связи фиксируется и затем автоматически заносится в поля связи дочерних записей. Метод, когда пользователь вручную заносит значения полей связи в дочерние записи, непопулярен: пользователь может внести одинаковое по смыслу, но разное по написанию значение ("Сахар", "сахар"). Много реже практикуется способ ввода дочерних записей без указания значения поля связи. Затем записи родительской и дочерних таблиц "связываются".Каскадные изменения могут блокироваться: или одновременно изменения и удаления, или изменения или удаления по отдельности. Необходимость разрешения или запрещения каскадных изменений обычно реализуется в СУБД при определении связей между таблицами. Собственно, таким образом и происходит создание ссылочной целостности. Обычно в СУБД для реализации ссылочной целостности в дочерней таблице создают внешний ключ (см. ниже), ссылающийся на родительскую таблицу, и указывают вид каскадных
воздействий. В последующем СУБД сама при необходимости реализует каскадные воздействия данного вида для указанных таблиц.