Изменение определения таблицы (инструкция ALTER TABLE)
Удаление таблицы (инструкция DROP TABLE)
Создание таблиц (инструкция CREATE TABLE)
Стандартные Типы данных SQL
Каждый столбец базы данных имеет свой тип, указываемый при создании столбца.
В стандарте SQL определены следующие типы:
1. символьные:
CHARACTER (len); CHAR (len);
CHARACTER VARYING (len); CHAR VARYING (len); VARCHAR (len);
NATIONAL CHARACTER (len); NATIONAL CHAR (len); NCHAR (len);
NATIONAL CHARACTER VARYING (len);
NATIONAL CHAR VARYING (len); NCHAR VARYING (len);
2. двоичные:
BIT (len); BIT VARYING (len);
3. числовые:
NUMERIC; DECIMAL; DEC; INTEGER; INT; SMALLINT;
FLOAT; REAL; DOUBLE PRECISION;
4. даты/времени:
DATE; TIME; TIME WITH TIME ZONE;
TIMESTAMP; TIMESTAMP WITH TIME ZONE;
5. интервальный: INTERVAL.
Для символьных типов возможно указание фразы CHARACTER SET { set_name | using_form}, устанавливающей используемый набор символов.
Приведем описание наиболее часто используемых типов данных:
CHAR (num) – текстовая строка фиксированной длины (в памяти выделяется место под всю строку);
VARCHAR (num) – текстовая строка переменной длины, содержащая не более num символов (на диске выделяется место в зависимости от длины строки);
INTEGER или INT – целое;
NUMERIC – число с плавающей точкой, возможно определение числа знаков после запятой;
DECIMAL или DEC – число с плавающей точкой, возможно задание минимального значения точности;
FLOAT – число с плавающей точкой, позволяющее задавать точность (количество знаков после запятой);
REAL – число с плавающей точкой, точность которого определяется реализацией;
DATE – тип даты;
TIME WITH TIME ZONE – тип времени, содержащий поля, описывающие сдвиг зонального времени.
Типы данных, описывающие дату и время, состоят из нескольких полей, в которых хранятся части даты времени.
Так, тип DATE содержит поля YEAR, MONTH и DAY.
Тип TIME содержит поля HOUR, MINUTE и SECOND.
Тип TIMESTAMP содержит как поля даты, так и поля времени.
Значение типа TIMESTAMP записывается следующим образом: '10.1.2003 08:30:00'. Порядок следования полей при написании даты, как правило, определяется установками ОС компьютера.
Для работы с данными, имеющими тип даты/времени в языке SQL предусмотрены следующие функции:
CURRENT_TIME – определяет текущее время;
CURRENT_DATE – определяет текущую дату;
CURRENT_TIMESTAMP – определяет текущую дату и время.
Инструкция CREATE TABLE, стандартная синтаксическая диаграмма которой изображена на рисунке, определяет новую таблицу и подготавливает ее к приему данных. Различные предложения инструкции задают элементы определения таблицы. Синтаксическая диаграмма инструкции кажется довольно громоздкой, поскольку требуется указать много элементов и параметров для них, однако на практике же создать таблицу относительно несложно.
После выполнения инструкции CREATE TABLE в БД появляется новая таблица, которой присваивается имя, указанное в инструкции, и владельцем которой становится создавший её пользователь. Имя таблицы должно быть идентификатором, допустимым в SQL, и не должно конфликтовать с именами других таблиц в схеме. Созданная таблица является пустой, данных записываются в нее с помощью инструкции 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). Если после модификации условие остается истинным, такое изменение допускается; в противном случае СУБД отвергает изменения и выдает сообщение об ошибке.
Пример Создать таблицу orders в соответствии со следующей схемой данных
Если первичный или внешний ключ включает в себя только один столбец, либо если условие уникальности или условие на значение касаются одного столбца, стандарт разрешает использовать упрощенную форму записи инструкции 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 без каких-либо параметров.
Изменения в определении таблицы можно осуществить с помощью инструкции 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 position;
Стандарт требует, чтобы одна инструкция ALTER TABLE использовалась для удаления только одного столбца, но в ряде ведущих СУБД такое ограничение снято.
Следует учитывать, что операция удаления столбца вызывает те же проблемы целостности данных, которые были описаны ранее на примере обновления таблиц. В частности, при удалении столбца, являющегося первичным ключом в каком-либо отношении, связанные с ним внешние ключи становится недействительными. Похожая проблема возникает, когда удаляется столбец, участвующий в проверке ограничения на значения другого столбца.
Описанные проблемы решаются так же, как и в случае инструкций DELETE и UPDATE – с помощью правила удаления. Можно выбрать одно из двух правил:
RESTRICT: если с удаляемым столбцом связан какой-либо объект в базе данных (внешний ключ, ограничение и т.п.), инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален;
CASCADE: любой объект базы данных (внешний ключ, ограничение и т.п.), связанный с удаляемым столбцом, также будет удален.
Правило CASCADE может вызвать целую “лавину” изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внешние ключи или ограничения обрабатывать с помощью дополнительных инструкций.
Изменение первичных и внешних ключей.Еще одним распространенным случаем применения инструкции ALTER TABLE является изменение или добавление определений первичных и внешних ключей таблицы.
Используя инструкцию ALTER TABLE, определения первичного и внешних ключей (в отличие от определений столбцов) можно как добавлять в таблицу, так и удалять из нее. Предложения, добавляющие определения первичного и внешнего ключей, являются точно такими же, как в инструкции CREATE TABLE, и выполняют те же функции.
Предложения, удаляющие первичный или внешний ключи, являются довольно простыми, однако удалить внешний ключ можно только тогда, когда создаваемая им связь имеет имя. Если имя присвоено не было, то задать эту связь в инструкции ALTER TABLE невозможно. В этом случае для удаления внешнего ключа необходимо удалить таблицу и воссоздать ее в новом виде.
Пример
alter table staff
add constraint works_in
foreign key (city) references cities;
При создании реляционной БД необходимо помнить о непротиворечивости и правильности хранимой информации. Для этого любая СУБД предусматривает возможность устанавливать одно или несколько условий целостности данных. Эти условия определяют, какие значения могут быть записаны в базу данных в результате добавления или обновления данных. Как правило, можно использовать следующие условия целостности данных:
- Обязательное наличие данных. По сути хранимой информации некоторые столбцы в базе данных должны содержать значения в каждой строке; строки в таких столбцах не могут содержать значения 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 самым простым способом решения этой проблемы является программное включение/отключение всех ограничений на время добавления данных в таблицы.
