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

Определение табличного ограничения

Элемент определения табличного ограничения целостности задается в следующем синтаксисе:

base_table_constraint_definition ::= 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE } ( column_commalist )
    | FOREIGN KEY ( column_commalist ) 
	    references_definition
    | CHECK ( conditional_expression )

Как мы видим, имеется три разновидности табличных ограничений: ограничение первичного или возможного ключа ( PRIMARY KEY или UNIQUE ), ограничение внешнего ключа ( FOREIGN KEY ) и проверочное ограничение ( CHECK ). Любому ограничению может явным образом назначаться имя, если перед определением ограничения поместить конструкцию CONSTRAINT constraint_name.

Табличное ограничение первичного или возможного ключа

Табличное ограничение первичного или возможного ключа   С учетом замечания по поводу особого толкования семантики неопределенных значений, сделанного в предыдущей сноске. ). Ограничение PRIMARY KEY, в дополнение к этому, влечет ограничение NOT NULL для всех столбцов, упоминаемых в определении ограничения. В определении таблицы допускается произвольное число определений возможного ключа (для разных комбинаций столбцов), но не более одного определения первичного ключа. Обратите особое внимание на последнюю часть предыдущего предложения: в языке SQL действительно допускается определение таблиц, у которых отсутствуют возможные ключи. Эта особенность языка, среди прочего, очевидным образом противоречит базовым требованиям реляционной модели данных.

Проверочное табличное ограничение

Определение табличного ограничения вида CHECK (conditional_expression) приводит к тому, что указанное условное выражение будет вычисляться при каждой попытке обновления соответствующей таблицы (вставке новой строки, удалении или модификации существующей строки). Считается, что попытка обновления таблицы нарушает проверочное ограничение целостности, если после выполнения операции обновления вычисление условного выражения дает результат false. Другими словами, таблица находится в соответствии с данным проверочным табличным ограничением, если для всех строк таблицы результатом вычисления соответствующего условного выражения не является false.

Мы отложим обсуждение допустимых разновидностей условных выражений до следующей лекции, где оно будет более уместно в контексте рассмотрения оператора SELECT языка SQL.

Табличное ограничение внешнего ключа

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

Табличное ограничение FOREIGN KEY (column_commalist) references_definition означает объявление внешним ключом группы столбцов, имена которых перечислены в списке column_commalist. Обсудим теперь смысл ограничения внешнего ключа при разных вариантах формирования определения ссылок ( references_definition ). Для удобства повторим синтаксическое правило.

references_definition ::= 
    REFERENCES base_table_name [ (column_commalist) ]
        [ MATCH { SIMPLE | FULL | PARTIAL } ]
        [ ON DELETE referential_action ]
        [ ON UPDATE referential_action ]

В этом определении base_table_name должно представлять собой имя некоторой базовой таблицы (пусть, например, эта таблица имеет имя T ). Если определение ссылок включает список столбцов ( column_commalist ), то этот список должен совпадать (с точностью до порядка следования имен столбцов) со списком имен столбцов, использованных в некотором определении первичного или возможного ключа ( PRIMARY KEY или UNIQUE ) в определении таблицы T. Если в определении ссылок список столбцов явно не задан, то считается, что он совпадает со списком столбцов, использованных в определении первичного ключа ( PRIMARY KEY ) таблицы T.

Разновидности способов сопоставления значений внешнего и возможного ключей

Пусть определяемая таблица имеет имя S. Обсудим смысл необязательного раздела определения внешнего ключа MATCH { SIMPLE | FULL | PARTIAL }. Если этот раздел отсутствует или если присутствует и имеет вид MATCH SIMPLE, то ограничение внешнего ключа (ссылочное ограничение) удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

Если раздел MATCH присутствует в определении внешнего ключа и имеет вид MATCH PARTIAL, то ограничение внешнего ключа удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

Если раздел MATCH имеет вид MATCH FULL, то ограничение внешнего ключа удовлетворяется в том и только в том случае, когда для каждой строки таблицы S выполняется одно из следующих условий:

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

Поддержка ссылочной целостности и ссылочные действия

В связи с определением ограничения внешнего ключа нам осталось рассмотреть еще два необязательных раздела – ON DELETE referential_action и ON UPDATE referential_action. Прежде всего, приведем синтаксическое правило:

referential_action ::= 
	{ NO ACTION | RESTRICT | CASCADE 
    | SET DEFAULT | SET NULL }

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

Теперь приступим к ссылочным действиям. Пусть определение ограничения внешнего ключа содержит раздел ON DELETE referential_action. Предположим, что предпринимается попытка удалить строку t из таблицы T. Тогда:

Пусть определение ограничения внешнего ключа содержит раздел ON UPDATE referential_action. Предположим, что предпринимается попытка обновить столбцы соответствующего возможного ключа в строке t из таблицы T. Тогда:

Примеры определений базовых таблиц

Определим таблицы служащих ( EMP ), отделов ( DEPT ) и проектов ( PRO ). Эти таблицы имеют заголовки, показанные на рис. 12.1.

Столбцы EMP_NO, EMP_SAL, DEPT_NO, PRO_NO, DEPT_TOTAL_SAL, DEPT_MNG и PRO_MNG определяются на ранее определенных доменах (определения доменов EMP_NO и SALARY приведены в предыдущей лекции). Первичными ключами отношений EMP, DEPT и проектов PRO являются столбцы EMP_NO, DEPT_NO и PRO_NO соответственно. В таблице EMP столбцы DEPT_NO и PRO_NO являются внешними ключами, указывающими на отдел, в котором работает служащий, и на выполняемый им проект соответственно. В таблице DEPT внешним ключом является столбец DEPT_MNG, указывающий на служащего, являющегося руководителем соответствующего отдела, а в таблице PRO внешним ключом является столбец PRO_MNG, указывающий на служащего, являющегося менеджером соответствующего проекта. Другие ограничения целостности мы обсудим позже.

Определим таблицу EMP:

(1)	CREATE TABLE EMP (
(2)	EMP_NO EMP_NO PRIMARY KEY,
(3)	EMP_NAME VARCHAR(20) DEFAULT 'Incognito' NOT NULL,
(4)	EMP_BDATE DATE DEFAULT NULL CHECK (
        VALUE >= DATE '1917-10-24'),
(5)	EMP_SAL SALARY,
(6)	DEPT_NO DEPT_NO DEFAULT NULL REFERENCES 
        DEPT ON DELETE SET NULL,
(7)	PRO_NO PRO_NO DEFAULT NULL,
(8)	FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO) 
        ON DELETE SET NULL,
(9)	CONSTRAINT PRO_EMP_NO CHECK
		((SELECT COUNT (*) FROM EMP E 
			WHERE E.PRO_NO = PRO_NO) <= 50));

Последовательно обсудим части этого определения. В части (1) указывается, что создается таблица с именем EMP. В части (2) определяется столбец EMP_NO на домене EMP_NO. У этого столбца не определено значение по умолчанию, и он объявлен первичным ключом таблицы (это ограничение целостности добавляется через AND к ограничениям, унаследованным столбцом от определения домена). Помимо прочего, это означает неявное указание запрета для данного столбца неопределенных значений. В части (3) определен столбец EMP_NAME на базовом типе данных символьных строк переменной длины с максимальной длиной 20. Для столбца указано значение по умолчанию – строка 'Incognito', и в качестве ограничения целостности запрещены неопределенные значения. В части (4) определяется столбец EMP_BDATE (дата рождения служащего). Он имеет тип данных DATE, значением по умолчанию является NULL (даты рождения некоторых служащих неизвестны). Кроме того, ограничение столбца запрещает принимать на работу лиц, о которых известно, что они родились до Октябрьского переворота. В части (5) определен столбец EMP_SAL на домене SALARY. Значение по умолчанию и ограничения целостности наследуются из определения домена. В части (6) столбец DEPT_NO определяется на одноименном домене (для наших целей его определение несущественно), но явно объявляется, что значением по умолчанию этого столбца будет NULL (некоторые служащие не приписаны ни к какому отделу). Кроме того, добавляется ограничение внешнего ключа: столбец DEPT_NO ссылается на первичный ключ таблицы DEPT. Определено ссылочное действие: при удалении строки из таблицы DEPT во всех строках таблицы EMP, ссылавшихся на эту строку, столбцу DEPT_NO должно быть присвоено неопределенное значение. В части (7) определяется столбец PRO_NO. Его определение аналогично определению столбца   DEPT_NO, но ограничение внешнего ключа вынесено в часть (8), где оно определяется в полной форме как табличное ограничение. Наконец, в части (9) определяется табличное проверочное ограничение с именем PRO_EMP_NO, которое требует, чтобы ни в одном проекте не участвовало больше 50 служащих (правила построения соответствующего условного выражения поясняются в следующих лекциях).

Определим таблицу DEPT:

(1)	CREATE TABLE DEPT (
(2)	DEPT_NO DEPT_NO PRIMARY KEY,
(3)	DEPT_EMP_NO INTEGER NOT NULL CHECK (
        VALUE BETWEEN 1 AND 100),
(4)	DEPT_NAME VARCHAR(200) DEFAULT 'Nameless' NOT NULL,
(5)	DEPT_TOTAL_SAL SALARY DEFAULT 1000000.00
	NOT NULL CHECK (VALUE > = 100000.00),
(6)	DEPT_MNG EMP_NO DEFAULT NULL 
		REFERENCES EMP ON DELETE SET NULL
		CHECK (IF (VALUE IS NOT NULL) THEN
			((SELECT COUNT(*) FROM DEPT
				WHERE DEPT.DEPT_MNG = VALUE) = 1),
(7)	CHECK (DEPT_EMP_NO = 
		(SELECT COUNT(*) FROM EMP 
			WHERE DEPT_NO = EMP.DEPT_NO)),
(8)	CHECK (DEPT_TOTAL_SAL >= 
		(SELECT SUM(EMP_SAL) FROM EMP 
			WHERE DEPT_NO = EMP.DEPT_NO)));

Это определение мы обсудим в менее систематической манере, чем предыдущее. Отметим только наиболее интересные моменты. В части (3) столбец Другими словами, это естественное ограничение требует, чтобы значения столбца По этой причине мы ввели в предыдущей лекции такую большую верхнюю границу – 20000000.00 – значений домена Другими словами, это естественное ограничение требует, чтобы размер фонда заработной платы отдела никогда не был меньше суммарной зарплаты, получаемой служащими этого отдела. Обратите внимание на определение столбца   DEPT_MNG – часть (6). Этот столбец объявляется внешним ключом таблицы DEPT. Но мы хотим сказать больше. У отдела могут временно отсутствовать руководители, поэтому в столбце допускаются неопределенные значения. Но если у отдела имеется руководитель, то он должен являться руководителем только этого отдела. На первый взгляд можно было бы воспользоваться ограничением столбца UNIQUE. Но такое ограничение допускало бы наличие неопределенного столбца DEPT_MNG только в одной строке таблицы DEPT, а мы хотим допустить отсутствие руководителя у нескольких отделов. Поэтому потребовалось ввести более громоздкое проверочное ограничение столбца.

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

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

Вот ответ на второй вопрос. Ограничение (9) в первом определении и ограничения (7) и (8) во втором определении внешне похожи, но сильно отличаются по своей сути. Ограничения (7) и (8) связаны с агрегатной семантикой столбцов DEPT_EMP_NO и DEPT_TOTAL_SAL таблицы DEPT. Отмена ограничений изменила бы смысл этих столбцов. Ограничение (9) является текущим административным ограничением. Если руководство предприятия примет решение разрешить использовать в одном проекте более 50 служащих, ограничение можно отменить без изменения смысла столбцов таблицы EMP. Имея это в виду, мы ввели явное имя ограничения (9), чтобы при необходимости имелась простая возможность отменить это ограничение с помощью оператора ALTER TABLE .

Наконец, определим таблицу PRO.

(1)	CREATE TABLE PRO (
(2)	PRO_NO PRO_NO PRIMARY KEY,
(3)	PRO_TITLE VARCHAR(200)DEFAULT 'No title' NOT NULL,
(4)	PRO_SDATE DATE DEFAULT CURRENT_DATE NOT NULL,
(5)	PRO_DURAT INTERVAL YEAR DEFAULT INTERVAL '1' 
        YEAR NOT NULL,
(6)	PRO_MNG EMP_NO UNIQUE NOT NULL 
        REFERENCES EMP ON DELETE NO ACTION,
(7)	PRO_DESC CLOB(10M));

Столбец PRO_SDATE содержит дату начала проекта, а столбец PRO_DURAT – продолжительность проекта в годах. В этом определении имеет смысл прокомментировать часть (6). Мы считаем, что если отдел, по крайней мере временно, может существовать без руководителя, то у проекта всегда должен быть менеджер. Поэтому определение столбца   PRO_MNG является гораздо более строгим, чем определение столбца   DEPT_MNG в таблице DEPT. Сочетание ограничений UNIQUE и NOT NULL при отсутствии значений по умолчанию приводит к абсолютной уникальности значений столбца PRO_MNG. Другими словами, этот столбец обладает всеми характеристиками первичного ключа, хотя объявлен только как возможный ключ. Кроме того, он объявлен как внешний ключ с действием при удалении строки таблицы EMP с соответствующим значением первичного ключа NO ACTION, запрещающим такие удаления. В совокупности это гарантирует, что у любого проекта будет существовать менеджер, являющийся служащим предприятия. В части (7) столбец PRO_DESC (описание проекта) определен как большой символьный объект с максимальным размером 10 Мбайт.

Изменение определения базовой таблицы

Оператор изменения определения базовой таблицы   ALTER TABLE имеет следующий синтаксис:

base_table_alteration ::= ALTER TABLE base_table_name
    column_alteration_action
    |  base_table_constraint_alternation_action

Как видно из этого синтаксического правила, при выполнении одного оператора ALTER TABLE может быть выполнено либо действие по изменению определения столбца, либо действие по изменению определения табличного ограничения целостности.

Добавление, изменение или удаление определения столбца

Действие по изменению определения столбца специфицируется в следующем синтаксисе:

column_alteration_action ::= 
        ADD [ COLUMN ] column_definition 
    | ALTER [ COLUMN ] column_name
        { SET default_definition | DROP DEFAULT }
    | DROP [ COLUMN ] column_name
					{ RESTRICT | CASCADE }

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

Смысл действия ADD COLUMN почти полностью совпадает со смыслом раздела определения столбца в операторе CREATE TABLE . Указывается имя нового столбца, его тип данных или домен. Могут определяться значение по умолчанию и ограничения целостности. Однако имеется одно существенное отличие: столбец, определяемый в действии ADD оператора ALTER TABLE , добавляется к уже существующей таблице, которая, скорее всего, содержит некоторый набор строк. В каждой из существующих строк новый столбец должен содержать некоторое значение, и считается, что сразу после выполнения действия ADD этим значением является значение столбца по умолчанию. Поэтому столбец, определяемый в действии ADD, обязательно должен иметь значение по умолчанию, т. е. для него недопустима ситуация, когда значением по умолчанию явно или неявно объявлено неопределенное значение ( NULL ), но среди ограничений целостности столбца присутствует ограничение NOT NULL .

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

Действие DROP COLUMN отменяет определение существующего столбца (удаляет его из таблицы). Действие DROP COLUMN отвергается, если:

Если в действии присутствует спецификация CASCADE, то его выполнение порождает неявное выполнение оператора DROP для всех представлений и ограничений целостности, в определении которых используется данный столбец.

Примеры изменения определения столбца

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

ALTER TABLE EMP
ADD EMP_BONUS SALARY DEFAULT NULL 
	CONSTRAINT BONSAL CHECK (VALUE < EMP_SAL);

Обратите внимание, что мы присвоили проверочному ограничению столбца явное имя, чтобы в случае, если ограничения на размер премии изменятся (что вполне возможно), можно было бы легко отменить это ограничение, воспринимая его как табличное.

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

ALTER TABLE EMP ALTER EMP_SAL SET DEFAULT 15000.00.

При определении столбца   DEPT_TOTAL_SAL таблицы DEPT для него было установлено значение по умолчанию 1000000. Главный бухгалтер предприятия может быть недоволен тем, что такие важные данные, как объем фонда зарплаты отделов, могут устанавливаться по умолчанию. Тогда можно отменить это значение по умолчанию:

ALTER TABLE DEPT ALTER DEPT_TOTAL_SAL DROP DEFAULT.

Обратите внимание, что после выполнения этого оператора при вставке новой строки в таблицу DEPT всегда потребуется явно указывать значение столбца DEPT_TOTAL_SAL. Хотя формально у столбца будет существовать значение по умолчанию, наследуемое от домена SALARY (10000.00), оно не может быть занесено в таблицу DEPT, поскольку противоречит ограничению столбца DEPT_TOTAL_SAL CHECK (VALUE >= 100000.00).

Можно задуматься, действительно ли требуется поддерживать в таблице DEPT столбец DEPT_EMP_NO. Как мы видели, для его поддержки требуется проверять громоздкое ограничение целостности, а число служащих в любом отделе можно получить динамически с помощью простого запроса к таблице EMP (собственно, этот запрос входит в ограничение целостности). Поэтому может оказаться разумным отменить определение столбца   DEPT_EMP_NO, выполнив следующий оператор ALTER TABLE :

ALTER TABLE DEPT DROP DEPT_EMP_NO CASCADE.

Напомним, что спецификация CASCADE ведет к тому, что при выполнении оператора будет уничтожено не только определение указанного столбца, но и определения всех ограничений целостности и представлений, в которых используется уничтожаемый столбец. В нашем случае единственное связанное с этим столбцом ограничение целостности, определенное вне определения столбца, было бы отменено, даже если бы в операторе отмены определения столбца   DEPT_EMP_NO содержалась спецификация RESTRICT, поскольку это единственное внешнее определение ограничения является ограничением только столбца DEPT_EMP_NO.

Изменение набора табличных ограничений

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

base_table_constraint_alternation_action ::=
        ADD [ CONSTRAINT ] base_table_constraint_definition
    | DROP CONSTRAINT constraint_name
        { RESTRICT | CASCADE }

Действие ADD [ CONSTRAINT ] позволяет добавить к набору существующих ограничений таблицы новое ограничение целостности. Можно считать, что новое ограничение добавляется через AND к конъюнкции существующих ограничений, как если бы оно определялось в составе оператора CREATE TABLE . Но здесь имеется одно существенное отличие. Если внимательно посмотреть на все возможные виды табличных ограничений, можно убедиться, что любое из них удовлетворяется на пустой таблице. Поэтому, какой бы набор табличных ограничений ни был определен при создании таблицы, это определение является допустимым и не препятствует выполнению оператора CREATE TABLE . При добавлении нового табличного ограничения с использованием действия ADD [ CONSTRAINT ] мы имеем другую ситуацию, поскольку таблица, скорее всего, уже содержит некоторый набор строк, для которого условное выражение нового ограничения может принять значение false. В этом случае выполнение оператора ALTER TABLE , включающего действие ADD [ CONSTRAINT ], отвергается.

Выполнение действия Хотя формально требуется указывать одно из этих ключевых слов в любом действии DROP CONSTRAINT .. При указании RESTRICT действие отвергается, если на данный возможный ключ ссылается хотя бы один внешний ключ. При указании CASCADE действие DROP CONSTRAINT выполняется в любом случае, и все определения таких внешних ключей также отменяются.

Примеры изменения набора табличных ограничений

Напомним, что мы добавили к таблице EMP столбец EMP_BONUS, в котором сохраняются размеры ежемесячных премий служащих. Предположим, что премии выплачиваются из фонда заработной платы отдела, в котором работает служащий. Тогда проверочное ограничение столбца   DEPT_TOTAL_SAL, устанавливающее, что объем фонда зарплаты отдела не должен быть меньше суммарной зарплаты служащих этого отдела, становится недостаточным, и нам требуется добавить к набору ограничений таблицы DEPT новое ограничение:

ALTER TABLE DEPT ADD CONSTRAINT TOTAL_INCOME
CHECK (DEPT_TOTAL_SAL >= 
    (SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0))
	FROM EMP WHERE EMP.DEPT_NO = DEPT_NO)).

Хотя это ограничение на вид довольно сложное, смысл его очень прост: суммарный доход служащих отдела не должен превышать объем зарплаты отдела. В арифметическом выражении под знаком агрегатной операции SUM используется операция COALESCE. Эта двуместная операция определяется следующим образом:

COALESCE (x, y) IF x IS NOT NULL THEN x ELSE y,

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

Понятно, что новое ограничение столбца DEPT_TOTAL_SAL сильнее предыдущего, и это предыдущее ограничение можно было бы отменить. Конечно, с логической точки зрения наличие обоих ограничений ничему не повредит (предыдущее ограничение является логическим следствием нового), но при использовании не слишком интеллектуальной реализации SQL может привести к замедлению работы системы, поскольку оба ограничения могут проверяться независимо. К сожалению, при определении таблицы EMP мы не присвоили явное имя проверочному ограничению столбца   DEPT_TOTAL_SAL и поэтому не можем немедленно продемонстрировать оператор отмены этого ограничения. Это не значит, что его нельзя отменить вообще. В стандарте языка SQL требуется, чтобы ограничения целостности, которым не назначены явные имена, получали имена, автоматически генерируемые системой. Любой квалифицированный пользователь SQL-ориентированной СУБД (скорее всего, администратор) может обнаружить имя любого ограничения, обратившись к системной таблице-каталогу ограничений целостности.

Кстати, новому ограничению мы присвоили явное имя. К этому привели следующие рассуждения. Когда создавалась исходная схема базы данных, руководство предприятия ничего не говорило о премиях служащих. Теперь начальство решило, что премии будут выплачиваться из фонда зарплаты. Для этого, мы добавили новый столбец и новое ограничение целостности. Но кто знает, не изменится ли снова решение о премиях? Чтобы не добавлять себе работы в будущем, дадим новому ограничению явное имя и не будем отменять предыдущее ограничение. Не следует расценивать эти рассуждения как руководство к действию. Мы привели их только для того, чтобы обосновать пример, хотя рассуждения, конечно, не лишены смысла.

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

ALTER TABLE EMP DROP CONSTRAINT PRO_EMP_NO;

Отмена определения (уничтожение) базовой таблицы

Для отмены определения (уничтожения) базовой таблицы служит оператор DROP TABLE , задаваемый в следующем синтаксисе:

DROP TABLE base_table_name { RESTRICT | CASCADE }

Успешное выполнение оператора приводит к тому, что указанная базовая таблица перестает существовать. Уничтожаются все ее строки, определения столбцов и табличные определения целостности. При наличии спецификации Не считая те ограничения целостности, которые (a) определены в составе определения данной базовой таблицы и (b) не ссылаются на какие-либо другие базовые таблицы .. При наличии спецификации CASCADE оператор выполняется в любом случае, и все определения представлений и ограничений целостности, содержащие ссылки на данную таблицу, также отменяются.

Средства определения и отмены общих ограничений целостности

Виды ограничений целостности, с которыми мы имели дело в предыдущих разделах этой лекции, образуют иерархию (рис. 12.2).

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

Но иерархия видов ограничений целостности этим не исчерпывается. Ограничения целостности, входящие в определение таблицы (включая явные и унаследованные от определения доменов ограничения столбцов), представляют собой ограничения базы данных, частью которой является данная таблица. Кроме того, могут определяться дополнительные ограничения базы данных. В стандарте SQL такие дополнительные ограничения базы данных называются ASSERTION, а мы их будем называть общими ограничениями целостности .

Определение общих ограничений целостности

Для определения общего ограничения целостности служит оператор CREATE ASSERTION , задаваемый в следующем синтаксисе:

CREATE ASSERTION constraint_name 
    CHECK (conditional_expression)

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

В определении таблицы EMP содержалось ограничение столбца EMP_BDATE:

CHECK (EMP_BDATE >= '1917-10-24')

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

CREATE ASSERTION MIN_EMP_BDATE CHECK
    (((SELECT MIN(EMP_BDATE)) FROM EMP) >= '1917-10-24')

В логическом условии этого общего ограничения выбирается минимальное значение столбца EMP_BDATE (дата рождения самого старого служащего). Значением условного выражения будет false в том и только в том случае, если среди служащих имеется хотя бы один, родившийся до указанной даты.

Теперь переформулируем в виде общего ограничения целостности ограничение таблицы EMP PRO_EMP_NO, которое определялось следующим образом:

CONSTRAINT PRO_EMP_NO CHECK
    ((SELECT COUNT (*) FROM EMP E 
        WHERE E.PRO_NO = PRO_NO) <= 50)

(над одним проектом не может работать более 50 служащих).

Вот формулировка эквивалентного общего ограничения целостности:

CREATE ASSERTION NEW_PRO_EMP_NO CHECK
    ( NOT EXISTS (SELECT PRO_NO FROM EMP GROUP BY PRO_NO
        HAVING COUNT(*) > 50)).

Логическое выражение этого ограничения может принимать только значения true и false. Внутренний оператор выборки группирует строки таблицы EMP таким образом, что в одну группу попадают все строки с одинаковым значением столбца PRO_NO. Затем эти группы фильтруются по условию раздела HAVING, и остаются только группы, включающие более 50 строк. В результирующей таблице содержатся строки из одного столбца, содержащего значение PRO_NO оставшихся групп. Предикат NOT EXISTS принимает значение true тогда и только тогда, когда эта результирующая таблица не содержит ни одной строки, т. е. нет ни одного проекта, в котором работает больше 50 служащих.

Покажем, как можно сформулировать в виде общего ограничения целостности ограничение внешнего ключа. Например, приведем такую эквивалентную формулировку для определения внешнего ключа PRO_NO, входящего в состав определения таблицы EMP:

FOREIGN KEY PRO_NO REFERENCES PRO (PRO_NO)

В виде общего ограничения целостности это может выглядеть следующим образом:

(1) CREATE ASSERTION FK_PRO_NO CHECK
(2) ( NOT EXISTS (SELECT * FROM EMP 
        WHERE PRO_NO IS NOT NULL AND
(3)	        NOT EXISTS (SELECT * FROM PRO
(4)	            WHERE PRO.PRO_NO = EMP.PRO_NO))).

Логическое выражение этого ограничения выглядит достаточно сложным и нуждается в пояснении. Условие выборки оператора SELECT на строке (2) состоит из двух частей, связанных через AND. Первая часть отфильтровывает те строки таблицы EMP, у которых в столбце PRO_NO содержится NULL. Если этот столбец содержит NULL во всех строках таблицы, то результирующая таблица оператора выборки на строке (2) будет пустой, и значением предиката NOT EXISTS будет true, т. е. ограничение удовлетворяется.

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

Если же найдется хотя бы одна строка таблицы EMP с таким значением cand_pro_no столбца PRO_NO, что в таблице PRO не найдется ни одной строки, значение столбца PRO_NO которой равнялось бы этому cand_pro_no, то результирующая таблица оператора выборки на строке (3) будет пустой, и значением предиката NOT EXISTS на строке (3) будет true. Тогда все условие выборки первого оператора SELECT примет значение true, и эта строка таблицы EMP будет пропущена в результирующую таблицу. Значением предиката NOT EXISTS будет false, т. е. ограничение не удовлетворяется.

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

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

(1) CREATE ASSERTION PRO_MNG_CONSTR CHECK
(2)	NOT EXISTS (SELECT * FROM EMP EMP1, EMP EMP2, 
                              DEPT, PRO WHERE 
(3)	        EMP1.EMP_NO = PRO.PRO_MNG AND
(4)	        EMP1.DEPT_NO = DEPT.DEPT_NO AND
(5)	        DEPT.DEPT_MNG = EMP2.EMP_NO AND
(6)	        EMP1.EMP_SAL + COALESCE (EMP1.EMP_BONUS,0) >
(7)	        EMP2.EMP_SAL + COALESCE (EMP2.EMP_BONUS,0);

В логическом выражении этого ограничения используется оператор выборки SELECT, в разделе перечня таблиц ( FROM ) впервые в этом курсе используется несколько таблиц. Такие запросы в SQL называются запросами с соединениями, и мы воспользуемся случаем, чтобы пояснить на примере (конечно, предварительно), как их следует понимать в соответствии со стандартом языка SQL.

Итак, в разделе FROM оператора выборки, используемого в логическом условии этого ограничения, через запятую перечислены четыре элемента – EMP EMP1, EMP EMP2, DEPT и PRO. Выражение вида EMP ANOTHER_NAME означает применение своего рода операции переименования. Внутри запроса столбцы этого "экземпляра" EMP имеют "квалифицированные" имена вида ANOTHER_NAME.column_name, где column_name обозначает имя существующего столбца таблицы EMP.

Вычисление оператора выборки начинается с того, что формируется расширенное декартово произведение всех таблиц, указанных в разделе Не следует воспринимать этот и следующие абзацы как описание того, как на самом деле выполняются подобные запросы в SQL-серверах. Это наиболее прямолинейный и малоэффективный способ выполнения запроса (хотя, в принципе, его можно применять и на практике). Мы выбрали этот способ описания, поскольку он максимально соответствует подходу к описанию семантики языка SQL, применяемому в стандарте языка. Кстати, основным отличием более практичных способов выполнения запросов с соединением является стремление к тому, чтобы избежать явного декартова произведения.

Условие раздела WHERE состоит из четырех частей, связанных через AND. Обсудим их последовательно. После проверки условия EMP1.EMP_NO = PRO.PRO_MNG в таблице ALL_TOGETHER останутся все служащие-менеджеры проектов вместе со своими проектами в комбинации со всеми возможными отделами и всеми возможными служащими (назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP1 ). После проверки условия EMP1.DEPT_NO = DEPT.DEPT_NO в таблице ALL_TOGETHER_STEP1 останутся все служащие-менеджеры проектов вместе со своими проектами и вместе с описанием своих отделов в комбинации со всеми возможными служащими (назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP2 ). После проверки условия DEPT.DEPT_MNG = EMP2.EMP_NO в таблице ALL_TOGETHER_STEP2 останутся все служащие-менеджеры проектов вместе со своими проектами, вместе с описанием своих отделов и вместе с руководителями этих отделов (по одной строке для каждого допустимого сочетания " проект-менеджер_проекта-отдел_менеджера_проекта-руководитель_отдела_менеджера_проекта "). Назовем эту отфильтрованную таблицу ALL_TOGETHER_STEP3. Легко видеть, что после проверки условия EMP1.EMP_SAL + EMP1.EMP_BONUS > EMP2.EMP_SAL + EMP2.EMP_BONUS в таблице ALL_TOGETHER_STEP3 могут остаться только строки проект-менеджер_проекта-отдел_менеджера_проекта-руководитель_отдела_менеджера_проекта, в которых суммарный доход менеджера проекта превышает суммарный доход руководителя отдела, где работает менеджер проекта. Если хотя бы одна такая строка существует, то результат оператора выборки будет непустым, значением предиката NOT EXISTS будет false, и тем самым ограничение целостности PRO_MNG_CONSTR будет нарушено.

Отмена определения общего ограничения целостности

Для того чтобы отменить ранее определенное общее ограничение целостности, нужно воспользоваться оператором DROP ASSERTION , задаваемым в следующем синтаксисе:

DROP ASSERTION constraint_name

Вот пример оператора, отменяющего определение дискриминационного общего ограничения целостности PRO_MNG_CONSTR:

DROP ASSERTION PRO_MNG_CONSTR;

Немедленная и откладываемая проверка ограничений

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

CHECK (DEPT_EMP_NO = 
    (SELECT COUNT(*) FROM EMP 
        WHERE DEPT_NO = EMP.DEPT_NO))

из определения таблицы DEPT. Предположим, например, что в отдел зачисляется новый служащий. Тогда нужно выполнить две операции: (a) вставить новую строку в таблицу EMP и (b) изменить соответствующую строку таблицы DEPT (прибавить единицу к значению столбца DEPT_EMP_NO ). Очевидно, что в каком бы порядке ни выполнялись эти операции, сразу после выполнения первой из них ограничение целостности будет нарушено, соответствующее действие будет отвергнуто, и мы никогда не сможем принять на работу нового служащего.

Поскольку ограничения целостности, немедленная проверка которых бессмысленна, являются нужными и полезными, в язык SQL включены средства, позволяющие регулировать время проверки ограничений. Если говорить более точно, в контексте каждой выполняемой транзакции каждое ограничение целостности должно находиться в одном из двух режимов: режиме немедленной проверки (immediate) или режиме отложенной проверки (deferred). Все ограничения целостности, находящиеся в режиме немедленной проверки, проверяются при выполнении в транзакции любой операции, изменяющей состояние базы данных. Если действие операции нарушает какое-либо немедленно проверяемое ограничение целостности, то это действие отвергается. Конечно, в грамотных реализациях SQL при выполнении операции проверяются не все немедленно проверяемые ограничения целостности, а только те, которые в принципе могут быть нарушены данной операцией. Ограничения целостности, находящиеся в режиме отложенной проверки, проверяются при завершении транзакции (выполнении операции Мы снова вынуждены забегать вперед. Средства SQL для управления транзакциями более подробно обсуждаются в следующих лекциях.. Если действия этой транзакции нарушают какое-либо отложенно проверяемое ограничение целостности, то транзакция откатывается (операция Конечно, в грамотных реализациях SQL при завершении транзакции проверяются не все отложенно проверяемые ограничения целостности, а только те, которые в принципе могут быть нарушены данной транзакцией.

Для этого в качестве заключительной синтаксической конструкции к любому определению ограничения целостности (любого вида) может быть добавлена спецификация INITIALLY в следующей синтаксической форме:

INITIALLY { DEFERRED | IMMEDIATE } 
    [ [ NOT ] DEFERRABLE ]

Эта спецификация указывает, в каком режиме должно находиться данное ограничение целостности в начале выполнения любой транзакции ( Для некоторых ограничений целостности режим отложенной проверки не имеет смысла. К таким ограничениям относятся, например, ограничения домена, ограничения NOT NULL и ограничения возможного ключа (хотя при их определении допускается указание DEFFERABLE ). Если же возможный ключ используется в некотором определении внешнего ключа, то в стандарте SQL требуется, чтобы ограничение этого возможного ключа было NOT DEFFERABLE . ).

Комбинация INITIALLY DEFERRED NOT DEFERRABLE является недопустимой. Если в определении ограничения спецификация начального режима проверки отсутствует, то подразумевается наличие спецификации INITIALLY IMMEDIATE. При наличии явной или неявной спецификации INITIALLY IMMEDIATE и отсутствии явного указания возможности смены режима подразумевается наличие спецификации NOT DEFERRABLE. При наличии спецификации INITIALLY DEFERRED и отсутствии явного указания возможности смены режима подразумевается наличие спецификации DEFERRABLE.

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

SET CONSTRAINTS { constraint_name_commalist | ALL }
        { DEFERRED | IMMEDIATE }

Если в операторе указывается список имен ограничений целостности, то все они должны быть DEFERRABLE ; если хотя бы для одного ограничения из списка это требование не выполняется, то операция SET CONSTRAINTS отвергается. При указании ключевого слова ALL режим устанавливается для всех ограничений, в определении которых явно или неявно было указано DEFERRABLE. Если в качестве желаемого режима проверки ограничений задано DEFERRED, то все указанные ограничения переводятся в режим отложенной проверки. Если в качестве желаемого режима проверки ограничений задано IMMEDIATE, то все указанные ограничения переводятся в режим немедленной проверки. При этом если хотя бы одно из этих ограничений не удовлетворяется, то операция SET CONSTRAINTS отвергается, и все указанные ограничения остаются в предыдущем режиме.

При выполнении операции COMMIT неявно выполняется операция SET CONSTRAINTS ALL IMMEDIATE. Если эта операция отвергается, то COMMIT срабатывает как ROLLBACK.

Заключение

В этой и предыдущей лекциях мы обсудили наиболее важные аспекты языка SQL, связанные с определением схемы базы данных, – типы данных SQL, средства определения доменов, базовых таблиц и ограничений целостности. Кроме того, были рассмотрены средства SQL, позволяющие динамически изменять и удалять определения этих объектов. Язык SQL устроен таким образом, что практически невозможно изложить какую-либо его часть независимо от других частей. И хотя эти две лекции по смыслу должны быть первыми среди лекций, посвященных SQL (было бы странно обсуждать операторы выборки строк из таблиц, вставки, изменения и удаления строк до обсуждения средств создания таблиц и ограничений целостности), нам пришлось забежать вперед и воспользоваться материалом следующих лекций для объяснения средств определения ограничений целостности. Надеюсь, что это не создало слишком больших неудобств для читателей, и отсутствие формальных определений удалось компенсировать наличием простых примеров.