Реализация ограничений целостности реляционной БД средствами языка SQL.

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

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

1 Целостность таблицы. Обязательно должны поддерживаться:
- уникальность строк таблицы. Должен быть определен первичный ключ таблицы, и значение его должно быть определено;
- все уникальные (потенциальные) ключи, выявленные в ходе анализа предметной области.
Эти ограничения реализуются в командах создания и модификации таблиц. Например, в языке SQL это команды Create Table, Alter Table. В этих командах для описания полей - первичных ключей используется конструкция primary key, для описания полей – уникальных ключей конструкция unique, обязательность значений полей задается конструкцией not null.

2 Декларативные ограничения данных. Так называют ограничения реляционной базы данных, объявленные предметной областью и выявленные в ходе её анализа. Задача проектировщика БД - адекватно отобразить их в БД.
Самые распространенные ограничения предметной области – это ограничения на свойства объекта предметной области, далее атрибута отношения или поля таблицы:
- обязательность значения поля;
- тип, длина, диапазон значения поля (например, значение должно быть целым и положительным), вхождение значения в заданный список и т.п.
Такие ограничения рекомендуется задавать на уровне домена в командах Create Domain, Alter Domain. Также они могут быть заданы в командах создания и модификации таблиц - Create Table, Alter Table при описании поля таблицы.

Эти ограничения также реализуются в командах создания и модификации таблиц (Create table, Alter table) при описании поля таблицы:
Value <оператор> <значение |
Value [Not] Between <знач.1> and <знач.2> |
Value [Not] In (<знач.1>[, <знач.2>,]) |
Value Is [Not] Null |
Value [Not] Like <знач.> [Escape <знач.>] |
Value [Not] Containing <знач.> |
Value [Not] Starting [With] <знач.> |

3 Ссылочная целостность. Каждая таблица проектируемой БД должна быть связана с другими посредством соответствующих первичных и внешних ключей, т.е. быть либо родительской (главной) по отношению к другим таблицам, либо дочерней (подчиненной), либо той и другой для разного уровня связей.
Назначение внешнего ключа - связывать каждую строку дочерней таблицы с соответствующей строкой родительской таблицы. Значение внешнего ключа может иметь и пустое значение (Null), если он реализует необязательную связь, выявленную в предметной области.
В качестве значения внешнего ключа может выступать значение и любого уникального (потенциального) ключа. Чтобы в физическом проекте реализовать поддержку ссылочной целостности, необходимо знать ситуации, когда она может быть нарушена:
1 группа ситуаций:
а) добавление строки в дочернюю таблицу
б) изменение значения ВК дочерней таблицы (перенос связи на другой объект)
В этом случае значение атрибута внешнего ключа новой строки должно соответствовать конкретному значению, присутствующему в одной из строк родительской таблицы, либо должно быть равно пустому значению (Null). В противном случае целостность будет нарушена;
2 группа ситуаций:
а) удаление строки из родительской таблицы. Ссылочная целостность будет нарушена, если в дочернем отношении существуют строки, ссылающиеся на удаляемую в родительской таблице строку. В этом случае может быть использована одна из следующих стратегий:
б) обновление первичного ключа в строке родительской таблицы. Редкая ситуация, рассматриваются все возможные стратегии, как и в случае 3).
1) No Action – удаление строки из родительской таблицы запрещено, если в дочерней таблице есть хотя бы одна ссылающаяся на неё строка;
2) Cascade (каскадное взаимодействие) – при удалении строки из родительской таблицы автоматически удаляются все ссылающиеся на нее строки дочерней таблицы. Если при этом любая из удаляемых строк дочерней таблицы выступает в качестве родительской для дочерних таблиц следующего уровня, то операция удаления применяется ко всем строкам дочерней таблицы этой связи и т.д. – удаление распространяется каскадно на все дочерние таблицы;
3) Set Null – при удалении строки из родительской таблицы во всех ссылающихся на неё строках дочерней таблицы в атрибутах внешнего ключа записывается пустое значение (Null);
4) Set Default – при удалении строки родительской таблицы значение атрибутов внешнего ключа ссылающейся на неё строки дочерней таблицы автоматически замещаются значениями по умолчанию, определенными при создании дочерней таблицы;
5) No Check – при удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности не предпринимается;
Связи между таблицами (ссылочная целостность) могут быть заданы либо путем явного описания внешних ключей в структурах таблиц (что является более предпочтительным, как и любое другое явное описание), либо ссылочная целостность может поддерживаться с помощью триггеров.
Например, для СУБД InterBase, если связь между двумя таблицами определена в команде Create Table при помощи конструкции foreign key, задающей явно поле – внешний ключ, ссылающийся на соответствующее поле - первичный ключ (конструкция references), то СУБД запрещает изменять значение первичного ключа, если на нее ссылаются какая-либо строка из дочерней таблицы, и удалять запись в родительской таблице, если на неё есть ссылающаяся запись из дочерней таблицы. Таким образом, связь, описанная в команде Create Table, блокирует каскадные изменения и удаления в родительской и дочерней таблицах, т.е. по умолчанию СУБД InterBase использует стратегию No Action.

Реализация средствами языка SQL
Команды CreateTable, AlterTable для дочерней таблицы.
Конструкции
Foreign Key <(имя поля)> References <имя родительской таблицы> <(имя поля – первичного ключа)>
Если явно задается внешний ключ, то разные СУБД могут использовать разные стратегии поддержки ссылочной целостности.