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

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

DROP TABLE base_table_name { RESTRICT | CADCADE }

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

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

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

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);

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

В логическом выражении этого ограничения используется оператор выборки 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будет нарушено.

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