Целостность данных.

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

Ограничения целостности имеют 3 компонента:

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

- логическое выражение (возможно в неявном виде), ограничение целостности не нарушается, когда оно истинно, и нарушается, когда оно ложно,

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

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

Ссылочная целостность. Такая целостность задается с использованием конструкции foreign key (см. выше). Реакция на нарушение целостности, заданной таким образом, - отказ от выполнения действий. Поэтому такая стратегия поддержки нарушения ссылочной целостности называется запрещающей – restrict. Существуют еще две стратегии поддержания ссылочной целостности – cascade (каскадная) и устанавливающая null-значение (set null).

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

Restrict:

- Restrict update со стороны родителя: нельзя модифицировать значение первичного ключа главной таблицы, если в потомке есть связанные записи.

- Restrict delete со стороны родителя: нельзя удалять записи из родительской таблицы, если в потомке есть связанные записи.

- Restrict insert со стороны потомка: нельзя вставить запись в потомка, если нет родителя с соответствующим значением первичного ключа.

- Restrict update со стороны потомка: при модификации записи в потомке нельзя установить значение ссылающихся полей на значение, отсутствующее в главной таблице.

Каскадная стратегия:

- Cascade delete со стороны родителя: при удалении записи-родителя удаляются все записи из потомка с соответствующими значениями внешнего ключа.

- Cascade update со стороны родителя: при модификации первичного ключа в родителе в потомках должны измениться значения полей внешнего ключа.

- Cascade insert со стороны потомка: при вставке потомка, не имеющего родителя, в родительскую таблицу вставляется запись c необходимым значением первичного ключа.

- Cascade insert со стороны родителя: при вставке родителя обязательно должен быть вставлен потомок с соответствующим значением первичного ключа.

Set null:

- Set null delete со стороны родителя: при удалении родителя значения полей внешнего ключа в потомке устанавливаются в null.

- Set null update со стороны родителя: при модификации первичного ключа в родителе в потомке внешний ключ устанавливается в null.

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

Стратегия set null неприменима для идентифицирующих связей, связей, не допускающих null-значение, а также для категорий.

Если необходимые стратегии не поддерживаются конструкцией foreign key для конкретной СУБД, то необходимо написать соответствующие триггеры.

Пример:

Две стратегии со стороны потомка поддерживаются конструкцией внешнего ключа. Стратегии со стороны родителя поддерживаются следующими триггерами:

create trigger del_gr

for gr

before delete

as

begin

update stnd

set numgr=null

where numgr=old.numgr

end

create trigger up_gr

for gr

before update

as

begin

update stnd

set numgr=new.numgr

end

Если какая-то стратегия не поддерживается по умолчанию, ее можно прописать:

Create table student ( stud_num int not null primary key

Numg: int

Fio : var char (255)

Foreign key (numgr)

references gruppa on (num_grupp)

on insert restrict

on update restrict)

Реализация более сложных правил целостности

Пример 1: пусть есть сущность «сотрудник» (табельный номер, ФИО, зарплата) и настроечная таблица «минимальная оплата труда» (величина). Нужно реализовать следующее правило: сотрудники не должны получать зарплату меньше, чем минимальная оплата труда. Если вносятся неверные значения, они должны заменяться на величину минимальной оплаты труда.

Нужны три триггера:

1. Create trigger ins_empl for Сотрудник after insert

As

Declare var z int;

Begin

Select величина from Мин. оплата труда into :z;

If (:z>new.зп) then new. Зп = z

End;

Похожий триггер нужен на изменение кортежа в таблице «Сотрудник». Третий триггер необходим при изменении таблицы «Минимальная оплата труда»

3. create trigger up_m for Мин. оплата труда after update

As

Begin

Update Сотрудник

Set z_p=new.з_п where z_p<new.з_п

End;

Пример 2. Зададим фрагмент схемы БД.

Реализовать следующие правила: состояние кредита: 0 – не возвращен, 1 – возвращен. Также нельзя оформить кредит клиенту, имеющему два или более непогашенных кредитов. Когда сумма выплат по кредиту станет равной сумме кредита, состояние кредита должно измениться на «возвращен». Если при выплате осуществлена переплата, то последнюю выплату необходимо отвергнуть.

1. Добавляем ограничение целостности:

Alter table кредит add check (состояние кредита in (0,1))

2. create exception bad_client ‘превышение количества непогашенных кредитов’

Create trigger ins_credit for кредит before insert

As

Declare variable n int;

Begin

Select count(*) from кредит

where (состояние кредита=0)and(номер клиента=new.номер клиента) into :c;

if (:c>2 then exception bad_client);

End;

3. create exception pere ‘переплата’

Create trigger ins_oplata for оплата

After insert

Declare variable s1 int;

Declare variable s2 int;

Select сумма к возврату

From кредит

Where номер кредита = new. Номер кредита

Into s1;

Select sum (сумма выплаты)

From выплата

Where номер кредита = new. Номер кредита

Into s2;

If (:s2>:s1) then exception pere

If (:s1=:s2) then update кредит set состояние кредита = 1

where номер кредита = new. Номер кредита

end;

Пример: рассмотрим полное разделение сущности «Студент» на категории. Корректными состояниями базы данных являются:

Ограничение целостности состояния поддерживается двумя триггерами: на вставку и на модификацию таблицы «Студент», которые в зависимости от значения дискриминатора заносят и удаляют записи в таблицах-категориях. Корректными являются переходы:

1=>2

1=>3

2=>1

Кроме того, человек может оказаться записанным в базу данных только в состоянии 1. Для поддержки целостности переходов необходимо изменить триггеры так, чтобы вставить можно было только запись со значением дискриминатора Д, а модификация проводилась только при корректных старом и новом значениях дискриминатора.