УСЛОВИЯ ЦЕЛОСТНОСТИ ДАННЫХ

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

- Обязательное наличие данных. По сути хранимой информации некоторые столбцы в базе данных должны содержать значения в каждой строке; строки в таких столбцах не могут содержать значения NULL. Например, в базе данных для каждого объекта недвижимости должен существовать владелец, сдающий этот объект. Поэтому столбец ono в таблице property_for_rent является обязательным и необходимо указать СУБД, что запись значения NULL в такой столбец недопустима.

- Условие на значение. Каждый столбец в базе данных определен на своем домене, т.е. имеет тот набор значений, которые допускается хранить в данном столбце. Можно указать СУБД, что запись значений, не входящих в определенный диапазон, в такие столбцы недопустима.

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

- Ссылочная целостность. Каждая строка таблицы-потомка с помощью внешнего ключа связана со строкой таблицы-предка, содержащей первичный ключ, значение которого равно значению внешнего ключа. Значение столбца bno таблицы staff связывает служащего с офисом, в котором он работает. Столбец bno должен содержать значение из столбца bno таблицы branch; в противном случае служащий будет закреплен за несуществующим офисом. Можно указать СУБД, чтобы она обеспечивала ограничение на значения внешнего ключа.

- Бизнес правила. Обновление информации в базе данных может быть ограничено деловыми правилами, которым подчиняются сделки, представляемые подобными обновлениями. Например, организация может установить деловое правило, запрещающее сотруднику офиса работать одновременно более чем с пятью объектами. Можно указать СУБД, что следует проверять каждую новую строку, добавляемую в таблицу property_for_rent , и убеждаться, что количество объектов, закрепленных за одним сотрудником, не превышает установленное деловое правило.

- Непротиворечивость. Многие реальные деловые операции вызывают в базе данных несколько изменений одновременно. Например, операция “увольнение сотрудника и передача всех закрепленных за ним объектов другим сотрудникам этого же отделения” может включать в себя удаление строки из таблицы staff с предварительным изменением содержимого соответствующих столбцов sno таблицы property_for_rent. Инструкции DELETE и UPDATE должны быть выполнены вместе для того, чтобы база данных осталась в правильном, непротиворечивом состоянии. Можно указать СУБД, что следует обеспечивать непротиворечивость изменяемых данных.

Обязательное наличие данных. Это условие целостности данных требует, чтобы некоторые столбцы не содержали значений NULL. Стандарт ANSI/ISO и большинство коммерческих СУБД поддерживают выполнение подобного условия, позволяя пользователю при создании таблицы объявить, что некоторые столбцы не могут содержать значений NULL. Условие задается как часть инструкции CREATE TABLE в виде ограничения NOT NULL.

Если на столбец наложено ограничение NOT NULL, то для выполнения этого условия СУБД обеспечивает следующее:

- ни в одной инструкции INSERT, добавляющей в таблицу строку или строки, нельзя указывать значение NULL для этого столбца; попытка добавить строку, содержащую (явно или неявно) значение NULL для такого столбца, вызовет ошибку;

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

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

Невозможность наложения ограничения NOT NULL на уже существующую таблицу является следствием того, что в большинстве СУБД значения NULL реализованы на внутреннем уровне. Обычно СУБД резервирует в каждой хранимой строке по одному дополнительному байту на каждый столбец, в котором значения NULL допустимы. Дополнительный байт служит “индикатором” значения NULL, и если в столбце содержится NULL, то этому байту присваивается заранее установленное значение. Если же для столбца определено ограничение NOT NULL, то байт индикатора отсутствует, что позволяет экономить дисковую память. Чтобы динамически отменить это ограничение, требуется “на ходу” переконфигурировать хранимые на диске записи, а в больших базах данных это весьма накладно.

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

Ограничение на значения столбца аналогично условию отбора в предложении WHERE и возвращает значение TRUE или FALSE. Если для столбца задано ограничение, то при каждом добавлении новой строки или обновлении старой СУБД автоматически проверяет, выполняется ли ограничение для значения в этом столбце. Если оно не выполняется, то инструкция INSERT или UPDATE завершается ошибкой. Ограничение на значения столбца задается при определении столбца в инструкции CREATE TABLE.

Целостность таблиц (сущностей). Каждая строка таблицы должна иметь уникальное значение первичного ключа, иначе база данных потеряет свою целостность. В ранних коммерческих СУБД первичные ключи отсутствовали, но сейчас они стали повсеместно распространенными. Первичные, ключи создаются с помощью инструкции CREATE TABLE. СУБД автоматически проверяет уникальность первичного ключа для каждой инструкции INSERT или UPDATE. Попытка добавить строку с уже существующим значением первичного ключа или обновить строку таким образом, что ее первичный ключ потеряет свою уникальность, завершится выдачей сообщения об ошибке.

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

- Добавление новой строки-потомка. Когда происходит добавление новой строки в таблицу staff, значение ее внешнего ключа bno должно быть равно одному из значений первичного ключа bno в таблице-предке branch. Если значение внешнего ключа не равно ни одному из значений первичного ключа, то добавление такой строки разрушит целостность базы данных, поскольку появится потомок без предка (“сирота”). Добавление строки в таблицу branch не вызовет проблем; она просто станет предком без потомков.

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

- Удаление строки-предка. Если из таблицы-предка branch будет удалена строка, у которой есть хотя бы один потомок, то строки-потомки станут сиротами. Значения внешних ключей в этих строках больше не будут равны ни одному из значений первичного ключа таблицы-предка. Удаление строки из таблицы-потомка не вызовет проблем. В этом случае предок этой строки после удаления будет иметь на одного потомка меньше.

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

Средства поддержки ссылочной целостности стандарта ANSI/ISO, позволяют обрабатывать каждую из четырех описанных ситуаций. Первая проблема решается путем проверки значений в столбцах внешнего ключа перед выполнением инструкции INSERT. Если они не равны ни одному из значений первичного ключа, то выполнение инструкции INSERT влечет появление сообщения об ошибке.

Вторая проблема (обновление таблицы – потомка) решается аналогично: путем проверки нового значения внешнего ключа. Если нет ни одного равного ему значения первичного ключа, инструкция UPDATE отбрасывается с выдачей сообщения об ошибке.

Третья проблема является более сложной. Предположим, например, что вы закрыли отделение в Гродно и хотите удалить соответствующую строку из таблицы branch. Как в этом случае поступить со строками-потомками таблицы staff зависит от ситуации:

- не удалять из базы данных отделение до тех пор, пока служащие не будут переведены в другое отделение;

- автоматически удалить всех соответствующих служащих из таблицы staff;

- в столбце staff.bno установить значение NULL для всех соответствующих служащих, показывая тем самым, что идентификатор их офиса неизвестен;

- в столбце staff.bno для соответствующих служащих установить по умолчанию некоторое значение, например идентификатор главного отделения в Минске, указывая тем самым, что служащие автоматически переводятся в это отделение.

Аналогичные сложности существуют и в четвертой ситуации. Допустим, по каким-либо причинам требуется изменить идентификатор отделения в Витебске с 1 на 2. Подобно предыдущему примеру, возникает вопрос о том, как поступить со строками-потомками в таблице staff, представляющими служащих Витебского офиса. И снова проблему можно решить четырьмя способами:

- не изменять идентификатор отделения до тех пор, пока служащие не будут переведены в другое отделение; в таком случае в таблицу branch следует вначале добавить строку с новым идентификатором отделения в Витебске, затем обновить таблицу staff и, наконец, удалить строку со старым идентификатором Витебского отделения;

- автоматически обновить идентификатор отделения этих служащих в таблице staff для того, чтобы их строки были по-прежнему связаны с Витебской строкой в таблице branch через ее новый идентификатор;

- в столбце staff.bno установить для соответствующих служащих значение NULL, показывая тем самым, что идентификатор офиса неизвестен;

- в столбце staff.bno установить по умолчанию для соответствующих служащих некоторое значение, например идентификатор главного офиса в Минске.

Правила удаления и обновления. Для каждого отношения предок/потомок в базе данных, создаваемого внешним ключом, стандарт SQL позволяет задать соответствующее правило удаления и соответствующее правило обновления. Правило удаления определяет те действия, которые СУБД выполняет, когда пользователь пытается удалить строку из таблицы-предка. Можно задать одно из четырех возможных правил удаления:

- RESTRICT (NO ACTION) – запрещает удаление строки из таблицы – предка, если строка имеет потомков. Следует также иметь в виду, что данное правило зачастую воспринимается СУБД по умолчанию и не требует явного задания.

- CASCADE – определяет, что при удалении строки-предка все строки-потомки также автоматически удаляются из таблицы-потомка. Данное правило опасно в употреблении, в случае если существуют многоуровневые связи между таблицами.

- SET NULL – определяет, что при удалении строки-предка внешним ключам во всех ее строках-потомках автоматически присваивается значение NULL.

- SET DEFAULT – определяет, что при удалении строки-предка внешним ключам, всем ее строкам-потомкам присваивается определенное значение, по умолчанию установленное для данного столбца.

Аналогично тому, как правила удаления определяют действия СУБД при попытке удалить строку из таблицы – предка, так правила обновления определяют действия СУБД, когда пользователь пытается обновить значение первичного ключа в таблице-предке. Регламентируются четыре правила обновления, аналогичные правилам удаления.

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

В заключение следует отметить, что некоторые СУБД, в частности ORACLE не поддерживают правил SET NULL и SET DEFAULT , а правило CASCADE требует явного задания и поддерживается только для удалений. Правила каскадного обновления в системе ORACLE недействительны, поддерживается только запрет на изменение строк-предков.

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

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

Рисунок Ссылочный цикл

На представленном рисунке таблица staff содержит столбец bno– внешний ключ для таблицы branch. Таблица branch, в свою очередь может содержать столбец mgr (с информацией об управляющих отделений) – внешний ключ для таблицы staff. Как видно из рисунка, эти два отношения образуют ссылочный цикл. Любая строка таблицы staff имеет ссылку на строку таблицы branch, которая имеет ссылку на строку таблицы staff, и т.д.

Независимо от количества таблиц, принимающих в них участие, ссылочные циклы представляют особую проблему ссылочной целостности. Предположим, например, что в двух таблицах, изображенных на рисунке, для первичных и внешних ключей не допускаются значения NULL. Рассмотрим, например, следующий запрос на добавление и соответствующие ему инструкции INSERT:

Поместить в базу данных информацию о новом сотруднике Иване Иванове с назначением его управляющим в только что открывшемся отделении в Москве:

insert into staff (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, 3);

insert into branch (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

Выполнение первой инструкции будет безрезультатным, потому что в новой строке есть ссылка на идентификатор отделения, которого в базе данных еще нет. Очевидно, что изменение порядка инструкции INSERT ни к чему не приведет, так как при выполнении инструкции для branch встречается ссылка на идентификатор служащего (руководитель офиса), который еще отсутствует в таблице staff.

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

insert into staff (sno, fname, lname, salary, bno)

values (23, ‘Иван’, ‘Иванов’, 500, null);

insert into branch (bno, street, area, city, mgr, tel_no)

values (3, ‘…’, ‘…’, ‘Москва’, 23, ‘…’);

update staff

set bno=3

where sno=23;

Как видно из данного примера, в некоторых ситуациях было бы удобно, чтобы условия ссылочной целостности вообще не проверялись до тех пор, пока не будет выполнен ряд взаимосвязанных обновлений. К сожалению, в большинстве современных СУБД отсутствует такой тип комплексной отложенной проверки. Для Oracle самым простым способом решения этой проблемы является программное включение/отключение всех ограничений на время добавления данных в таблицы.