Условия целостности данных

Изменение определения таблицы (инструкция ALTER TABLE)

Удаление таблицы (инструкция DROP TABLE)

Создание таблиц (инструкция CREATE TABLE)

Инструкция CREATE TABLE, стандартная синтаксическая диаграмма которой изображена на рисунке, определяет структуру новой таблицы БД и подготавливает ее к приему данных. Предложения инструкции задают основные элементы определения таблицы, такие как названия и тип данных, различные виды ограничений.

После выполнения инструкции CREATE TABLE в БД появляется новая таблица, которой присваивается имя, указанное в инструкции, и владельцем которой становится создавший её пользователь. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами других таблиц в схеме. Созданная таблица является пустой, данных записываются в нее с помощью DML инструкции INSERT INTO.

Определения столбцов. Столбцы новой таблицы задаются в инструкции CREATE TABLE. Определения столбцов представляют собой заключенный в скобки список, элементы которого отделены друг от друга запятыми. Порядок следования определений столбцов в списке соответствует расположению столбцов в таблице. При описании столбца указывается следующая информация:

- имя столбца, которое используется для ссылки на столбец в инструкциях SQL. Каждый столбец в таблице должен иметь уникальное имя, но в разных таблицах имена столбцов могут совпадать.

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

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

- указание на то, обязательно ли столбец должен содержать данные. Ограничение NOT NULL, если оно задано, следит за обязательным наличием данных в столбце при заполнении таблицы, в противном случае значения NULL допускаются.

Определение первичного и внешнего ключей. Кроме определений столбцов таблицы, в инструкции CREATE TABLE указывается информация о первичном ключе таблицы и ее связях с другими таблицами базы данных. Эта информация содержится в предложениях PRIMARY KEY и FOREIGN KEY.

В предложении PRIMARY KEY задается столбец, или столбцы, которые образуют первичный ключ таблицы. Как известно, этот столбец (или комбинация столбцов) служит в качестве уникального идентификатора строк таблицы. СУБД автоматически следит за тем, чтобы первичный ключ каждой строки таблицы имел уникальное значение. Кроме того, в столбцах, использующихся в качестве первичного ключа не должны допускаться значения NULL (имеют ограничение NOT NULL).

В предложении FOREIGN KEY задается внешний ключ таблицы и определяется столбец связи с другой таблицей. В нем указываются:

- необязательное имя связи для этого отношения после ключевого слова CONSTRAINT; оно не используется в инструкциях SQL, но может появляться в сообщениях об ошибках и потребуется, если будет необходимо удалить внешний ключ и разорвать связь между таблицами;

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

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

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

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

В стандартном SQL правил удаления, как и правил обновления всего четыре: RESTRICT, CASCADE, SET NULL и SET DEFAUL. Однако правила SET NULL и SET DEFAULT некоторыми СУБД вообще не поддерживаются, а правило RESTRICT не нуждается в явном указании, так как в большинстве случаев принимается по умолчанию. На синтаксической диаграмме для наглядности задания инструкции CREATE TABLE включено правило каскадного обновления (ON UPDATE CASCADE).

Непосредственно в Oracle существует 3 правила:

- CASCADE удаляет или обновляет родительскую запись и вместе с ней все дочерние.

- SET NULL устанавливает пустые значения для дочерних строк в случае удаления(но не обновления) родительской.

- NO ACTION (установлено по умолчанию) запрещает выполнение операций удаления или обновления родительской записи, если у нее есть дочерние.

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

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

Условия уникальности и ограничения на значения столбцов. В соответствии со стандартом условие уникальности значений в каком-либо столбце, не выбранном в качестве первичного ключа, задается в предложении UNIQUE инструкции CREATE TABLE. Это дополнительное ограничение, которое позволяет СУБД автоматически следить за уникальностью данных в указанном столбце при заполнении таблицы.

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

Пример: Создать таблицу с ограничениями уникальности и проверки значений

create table salesreps (

sale_n integer not null,

name varchar2(15),

position varchar2(10),

sex char(1),

salary float(2),

age integer,

unique (name),

primary key (sale_n),

check (age <=35),

check (sex in ('m', 'f ')),

check (salary <=500.00)

);

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

create table orders (

order_n integer not null,

ord_date date not null,

cust_n integer,

sale_n integer,

prod_n varchar2(10),

qty integeR,

amount integer,

primary key (order_n),

constraint placed_by foreign key (cust_n) references customers,

constraint taken_by foreign key (sale_n) references salesreps,

constraint is_for foreign key (prod_n) references products);

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

create table offices (

office_n integer not null primary key,

city varchar2(15) not null unique,

region_n varchar2(10) not null references regions);

С течением времени структура базы данных изменяется. Ненужные таблицы можно удалять посредством инструкции DROP TABLE. Стандартная синтаксическая диаграмма приведена на рисунке

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

В соответствии со стандартом инструкция DROP TABLE должна включать в себя либо параметр CASCADE, либо RESTRICT, которые определяют, как влияет удаление таблицы на другие объекты базы данных (например, представления), зависящие от этой таблицы. Если задан параметр CASCADE и в базе данных имеются объекты, которые содержат ссылку на удаляемую таблицу, то выполнение инструкции закончится неуспешно. В большинстве коммерческих СУБД допускается применение инструкции DROP TABLE без каких-либо параметров.

Синтаксическая диаграмма DROP TABLE в Oracle имеет следующий вид:

DROP TABLE [ schema.] table_name

[CASCADE CONSTRAINTS] [PURGE];

Конструкция CASCADE CONSTRAINTS позволит удалить таблицу, если на ее столбцы определены ссылки внешними ключами других таблиц (внешний ключ, ссылающийся на значения столбцов «собственной» таблицы, не препятствует ее удалению). При этом СУБД удалит сначала «мешающее» правило внешнего ключа, а столбцы другой, оставшейся таблицы в результате сохранят свои значения, но они уже не будут обременены ограничением ссылочной целостности. Фактически использование CASCADE CONSTRAINTS равносильно последовательному удалению всех правил внешнего ключа, ссылающихся на удаляемую таблицу (таковых может быть несколько), и выполнению простой команды DROP TABLE.

Конструкция PURGE появилась в 10 версии Oracle и изменила смысл команды DROP. Теперь после ее выполнения описание и данные таблицы продолжают храниться на своих местах, но под новыми, присвоенными системой автоматически именами. Для пользователя таблица удалена, однако все, что нужно для ее восстановления, если такая необходимость возникнет, продолжает храниться в БД. Тем самым для таблиц реализована техника «мусорной корзины» (recycle bin), хорошо известная по файловым системам.

Список содержимого мусорной корзины можно получить из системной таблицы USER_RECYCLEBIN (публичный синоним — RECYCLEBIN) командой:

SELECT object_name, original_name, droptime FROM user_recyclebin;

Восстановить таблицу по исходному имени (поле ORIGINAL_NAME из USER_RECYCLEBIN) можно, например, так:

FLASHBACK TABLE table_name TO BEFORE DROP;

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

Для удаления таблицы из мусорной корзины нужно использовать команду PURGE, например:

PURGE TABLE table_name;

Можно очистить корзину целиком:

PURGE RECYCLEBIN;

Чтобы таблица удалялась сразу без возможности восстановления, следует использовать конструкцию PURGE в команде DROP:

:

DROP TABLE table_name PURGE;

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

Как видно из рисунка, инструкция ALTER TABLE может:

- добавить в таблицу определение нового столбца;

- удалить существующий столбец из таблицы;

- изменить значение по умолчанию, для какого-либо столбца;

- добавить или удалить первичный ключ таблицы;

- добавить или удалить внешний ключ таблицы;

- добавить или удалить условие уникальности;

- добавить или удалить условие на значения.

В коммерческих СУБД некоторые из рассмотренных возможностей не используются вообще либо используются специфические предложения (так вместо регламентированного стандартом предложения ALTER на синтаксической диаграмме использовано ключевое слово MODIFY, специфичное для ORACLE) либо добавляются совершенно новые возможности изменения таблиц.

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

Добавление и удаление столбца. Чаще всего инструкция ALTER TABLE применяется для добавления столбца в существующую таблицу. Предложение с определением столбца в инструкции ALTER TABLE имеет точно такой же вид, как и в инструкции CREATE TABLE. Новое определение добавляется в конец определений столбцов таблицы, и в последующих запросах новый столбец будет крайним справа. СУБД обычно предполагает, что новый столбец во всех существующих строках содержит значения NULL. Если столбец объявлен как NOT NULL WITH DEFAULT, то СУБД считает, что он содержит значения по умолчанию. Нельзя объявлять столбец просто как NOT NULL, поскольку СУБД подставляла бы в существующие строки значения NULL, нарушая тем самым заданное условие. В действительности, когда вы добавляете новый столбец, СУБД не заносит во все существующие строки нового столбца значения NULL или значения по умолчанию. СУБД обнаруживает тот факт, что строка слишком коротка для нового определения таблицы, только при выборке этой строки пользователем, и расширяет ее значениями NULL или значениями по умолчанию непосредственно перед выводом на экран или передачей в программу пользователя.

С помощью инструкции ALTER TABLE можно удалить из существующей таблицы один или несколько столбцов, если в них больше нет необходимости. Например, следующая инструкция удаляет столбец position из таблицы staff.

ALTER TABLE staff

DROP COLUMN position;

Стандарт требует, чтобы одна инструкция ALTER TABLE использовалась для удаления только одного столбца, но в ряде ведущих СУБД такое ограничение снято.

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

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

RESTRICT: если с удаляемым столбцом связан какой-либо объект в базе данных (внешний ключ, ограничение и т.п.), инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален;

CASCADE: любой объект базы данных (внешний ключ, ограничение и т.п.), связанный с удаляемым столбцом, также будет удален.

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

Изменение первичных и внешних ключей. Еще одним распространенным случаем применения инструкции ALTER TABLE является изменение или добавление определений первичных и внешних ключей таблицы.

Используя инструкцию ALTER TABLE, определения первичного и внешних ключей (в отличие от определений столбцов) можно как добавлять в таблицу, так и удалять из нее. Предложения, добавляющие определения первичного и внешнего ключей, являются точно такими же, как в инструкции CREATE TABLE, и выполняют те же функции.

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

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

alter table staff

add constraint live_in

foreign key (adress) references adresses;

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

- Обязательное наличие данных. По сути хранимой информации некоторые столбцы в базе данных должны содержать значения в каждой строке; строки в таких столбцах не могут содержать значения 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, вызовет ошибку.

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

Ограничение на значения столбца аналогично условию отбора в предложении 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.bn o установить по умолчанию для соответствующих служащих некоторое значение, например идентификатор главного офиса в Минске.

Правила удаления и обновления. Для каждого отношения предок/потомок в базе данных, создаваемого внешним ключом, стандарт 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 sn o=23;

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

Если ограничения препятствуют вводу данных и их нужно временно отменить можно воспользоваться оператором:

ALTER TABLE table_name DISABLE CONSTRAINT имя_ограничения [CASCADE];

а затем можно снова включить его оператором:

ALTER TABLE table_name ENABLE CONSTRAINT имя_ограничения;

Дата добавления: 2014-01-04