2008 г.

Базы данных. Вводный курс

Сергей Кузнецов

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

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

DROP TABLE base_table_name { RESTRICT | CADCADE }

Успешное выполнение оператора приводит к тому, что указанная базовая таблица перестает существовать. Уничтожаются все ее строки, определения столбцов и табличные определения целостности. При наличии спецификации RESTRICT выполнение оператора DROP TABLE отвергается, если имя таблицы используется в каком-либо определении представления или ограничения целостности¹⁰⁸⁾. При наличии спецификации CASCADE оператор выполняется в любом случае, и все определения представлений и ограничений целостности, содержащие ссылки на данную таблицу, также отменяются.

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

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

Рис. 16.2. Иерархия видов ограничений целостности

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

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

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

Для определения общего ограничения целостности служит оператор 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, т. е. ограничение удовлетворяется.

Теперь предположим, что в таблице EMP нашлась строка emp, в столбце PRO_NO которой содержится значение, отличное от NULL. Назовем это значение cand_pro_no. Для него вычисляется вторая часть условия выборки оператора SELECT на строке (2). Оператор выборки на строке (3) выбирает все строки таблицы PRO, значение столбца PRO_NO которых равняется cand_pro_no. Если для данного значения cand_pro_no нашлась хотя бы одна такая строка, то результирующая таблица оператора выборки на строке (3) будет непустой, и значением предиката NOT EXISTS на строке (3) будет false. Соответственно, все условие выборки первого оператора SELECT примет значение false, и строка со значением cand_pro_no в столбце 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.

Вычисление оператора выборки начинается с того, что формируется расширенное декартово произведение всех таблиц, указанных в разделе FROM. В данном случае схема результирующей таблицы раздела FROM будет содержать следующие имена столбцов: EMP1.EMP_NO, EMP1.EMP_NAME, EMP1. EMP_BDATE, EMP1. EMP_SAL, EMP1.EMP_BONUS, EMP1. DEPT_NO, EMP1. PRO_NO, EMP2.EMP_NO, EMP2.EMP_NAME, EMP2. EMP_BDATE, EMP2. EMP_SAL, EMP2.EMP_BONUS, EMP2. DEPT_NO, EMP2. PRO_NO, DEPT.DEPT_NO, DEPT.DEPT_EMP_NO, DEPT.DEPT_TOTAL_SAL, DEPT.DEPT_MNG, PRO.PRO_NO, PRO.PRO_TITLE, PRO.PRO_SDATE, PRO.PRO_DURAT, PRO.PRO_MNG, PRO_DESC. Для удобства назовем эту «широкую» таблицу ALL_TOGETHER.¹¹⁰⁾

Условие раздела 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 будет нарушено.

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

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

DROP ASSERTION constraint_name

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

DROP ASSERTION PRO_MNG_CONSTR;

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

На первый взгляд кажется, что ограничения целостности (всех видов) должны немедленно проверяться в случае выполнения любого действия, изменяющего содержимое базы данных (вставка в любую таблицу новой строки, изменение или удаление существующих строк). Однако можно определить такие ограничения целостности, логическое выражение которых будет принимать значение 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). Все ограничения целостности, находящиеся в режиме немедленной проверки, проверяются при выполнении в транзакции любой операции, изменяющей состояние базы данных. Если действие операции нарушает какое-либо немедленно проверяемое ограничение целостности, то это действие отвергается.¹¹¹⁾ Ограничения целостности, находящиеся в режиме отложенной проверки, проверяются при завершении транзакции (выполнении операции COMMIT)¹¹²⁾. Если действия этой транзакции нарушают какое-либо отложенно проверяемое ограничение целостности, то транзакция откатывается (операция COMMIT трактуется как операция ROLLBACK).¹¹³⁾

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

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

Эта спецификация указывает, в каком режиме должно находиться данное ограничение целостности в начале выполнения любой транзакции (INITIALLY IMMEDIATE означает, что в начале выполнения транзакции данное ограничение будет находиться в режиме немедленной проверки, а INITIALLY DEFERRED – что в начале любой транзакции ограничение будет находиться в режиме отложенной проверки), а также возможности смены режима этого ограничения при выполнении транзакции (DEFERRABLE означает, что для данного ограничения может быть установлен режим отложенной проверки, а NOT DEFERRABLE – что не может¹¹⁴⁾).

Комбинация 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.

16.4. Заключение

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

108 Не считая те ограничения целостности, которые (a) определены в составе определения данной базовой таблицы и (b) не ссылаются на какие-либо другие базовые таблицы.

109 Это означает, что cand_pro_no является допустимым значением внешнего ключа.

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

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

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

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

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

Назад Содержание Вперёд