Главная » SQL, Базы данных » СРЕДСТВА SQL ограничения

0

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

■     В языке SQL вообще не поддерживаются ограничения типа, за исключением тех примитивных ограничений, которые являются прямым следствием применения определенного физического представления. Например, как было показано в главе 5, допустимо утверждать, что значения типа WEIGHT должны быть представлены в виде чисел DECIMAL (5,1), но нельзя указать, что эти числа должны быть больше нуля и меньше 5000.

■     В языке SQL (безусловно) поддерживаются ограничения атрибута.

■     В языке SQL не поддерживаются ограничения переменной отношения как таковые.

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

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

СЛОЖНОСТЬ.

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

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

Ограничения базовой таблицы

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

Примечание. Перед определением любого из этих ограничений может находиться необязательная  спецификация  CONSTRAINT  <constraint  name>,   которая  позволяет присвоить ограничению имя. Автор не рассматривает эту опцию для сокращения изложения (но необходимо отметить, что на практике, по-видимому, следует  присваивать имена всем ограничениям). Здесь также не рассматриваются  некоторые сокращения, например, возможность задавать потенциальный ключ (как "встроенный" в составе определения столбца), по той же причине.

Потенциальные ключи

Любое определение потенциального ключа SQL принимает одну из следующих двух форм.

PRIMARY KEY ( <column name commalist>

) UNIQUE ( <column name commalist> )

В обоих случаях выражение <column name commalist> с разделенным  запятыми списком имен столбцов не должно быть пустым20. Любая конкретная  базовая таблица может иметь не больше одной спецификации с  определением  первичного ключа PRIMARY KEY, но любое количество спецификаций потенциального ключа UNIQUE. В случае PRIMARY KEY каждый указанный столбец дополнительно рассматривается как соответствующий требованию NOT NULL, т.е. не содержащий пустых значений, даже если ключевое слово NOT NULL не задано явно (см. приведенное ниже описание ограничений проверки).

20 См. упражнение 9.10.

Внешние ключи

Определение внешнего ключа языка SQL принимает следующую форму.

FOREIGN KEY ( <colиmn name commalist> )

REFERENCES <base table name> [ ( <column name

commalist> ) ] [ ON DELETE <referential action> ] [ ON

UPDATE <referential action> ]

Здесь спецификация  ссылочного  действия <referential action> может  принимать значение NO ACTION (ПО умолчанию), RESTRICT, CASCADE, SET  DEFAULT ИЛИ SET NULL21. Отложим обсуждение конструкций SET DEFAULT и SET NULL до главы 19; другие опции  описаны  в  разделе  9.10.  Вторая  спецификация  <column  name  commalist> требуется, если внешний ключ ссылается на потенциальный ключ, который не является первичным ключом.

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

Ограничения проверки

Любое ограничение проверки в языке SQL принимает следующую форму.

CHECK  (  <bool  ехр>  )

Допустим, что СС — ограничение проверки для базовой таблицы т. В таком  случае считается, что в таблице т ограничение СС нарушается тогда и только тогда, когда эта таблица в настоящее время содержит по меньшей мере одну строку (см. последний абзац данного подраздела) и проверка текущего значения т влечет за собой то, что логическое выражение <bool ехр> для СС принимает значение FALSE.

Примечание. В общем, следует отметить, что выражения <bool ехр> языка SQL могут быть сколь угодно сложными; даже в данном рассматриваемом контексте они явно не ограничиваются ссылками только на базовую таблицу т, но могут вместо этого ссылаться на любую доступную часть базы данных.

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

CREATE TABLE SP

( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL,

PRIMARY KEY ( S#, P# ),

FOREIGN KEY ( S# ) REFERENCES S

ON DELETE CASCADE ON UPDATE

CASCADE, FOREIGN KEY ( P# )

REFERENCES P

ON DELETE CASCADE

ON UPDATE CASCADE, CHECK ( QTY

> QTY ( 0 ) AND QTY < QTY { 5000 ) ) ) ;

21  Кстати, следует отметить, что для поддержки некоторых действий типа <ref erential action> (в частности CASCADE) требуется, чтобы система (хотя бы неявно) поддерживала некоторые виды  операций  множественного реляционного  присваивания!  Причем  это  требование  существует  несмотря  на  то,  что  подобные  операции  не поддерживаются в языке SQL как таковые.

Здесь предполагается, что атрибуты s# и Р# были явно определены как предназначенные для использования, соответственно, в качестве первичных ключей для таблиц S и Р. Кроме того, в этом операторе используется сокращение, согласно которому ограничение проверки в указанной ниже форме может быть заменено простой спецификацией NOT NULL в определении рассматриваемого столбца <column name>.

CHECK ( <column name> IS NOT NULL )

Поэтому в данном примере три ограничения проверки, которые могли оказаться довольно сложными, заменены тремя спецификациями NOT NULL.

В заключение этого подраздела еще раз повторим замечание, что ограничение базовой таблицы SQL всегда считается удовлетворенным, если рассматриваемая базовая таблица оказалась пустой, даже если это ограничение имеет форму (скажем) "1 = 2" (или даже, если на то пошло, оно по сути имеет такую форму, что "эта таблица не должна быть пустой"!).

Утверждения

Теперь перейдем к описанию общих ограничений SQL, или утверждений. Такие ограничения определяются с помощью оператора CREATE ASSERTION, который имеет показанный ниже синтаксис.

CREATE ASSERTION <constraint

пате> CHECK ( <bool exp> )

;

А ниже показан синтаксис оператора DROP ASSERTION.

DROP ASSERTION <constraint name> ;

Обратите внимание на то, что в отличие от большинства других форм оператора DROP языка  SQL  (например,  DROP  TYPE,  DROP  TABLE,  DROP  VIEW),  В   операторе  DROP ASSERTION не предусмотрены две противоположные опции RESTRICT и CASCADE.

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

1.    Значение статуса каждого поставщика должно находиться в пределах от 1 до 100

включительно.

CREATE ASSERTION SC1 CHECK

( NOT EXISTS ( SELECT * FROM S

WHERE S.STATUS < 0    OR

S.STATUS > 100 ) ) ;

2.    Каждый поставщик из Лондона имеет статус 20.

CREATE ASSERTION SC2 CHECK

( NOT EXISTS ( SELECT * FROM S

WHERE S.CITY = ‘London’ AND    S.STATUS ≠ 20 ) )

;

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

CREATE ASSERTION РСЗ CHECK     ( NOT EXISTS ( SELECT * FROM P )

OR EXISTS ( SELECT * FROM P

WHERE P.COLOR = COLOR (‘Blue’) ) ) ;

4.  Разные поставщики не могут иметь одинаковые номера поставщиков.

CREATE ASSERTION SC4 CHECK

( UNIQUE ( SELECT S.S# FROM S ) ) ;

В этом операторе UNIQUE представляет собой операцию SQL, которая принимает в качестве фактического параметра таблицу и возвращает значение TRUE, если эта таблица не содержит дубликатов строк, а в противном случае возвращает значение FALSE.

5.  Каждая поставка выполняется существующим поставщиком.

CREATE ASSERTION SSP5 CHECK

. ( NOT EXISTS

( SELECT * FROM SP

WHERE NOT EXISTS

( SELECT * FROM S

WHERE S.S# = SP.S# ) ) ) ;

Ни один поставщик со статусом меньше 20 не поставляет любые детали в количестве больше 500.

CREATE ASSERTION SSP6 CHECK

( NOT EXISTS ( SELECT * FROM S, SP

WHERE S.STATUS < 20 AND   S.S#

= SP.S# AND   SP.QTY > QTY ( 500

) ) ) ;.

Кратко рассмотрим еще один пример. Для этого обратимся к приведенному ниже определению представления из предыдущего раздела.

CREATE VIEW LONDON_SUPPLIER AS SELECT S#, SNAME,

STATUS FROM  S WHERE CITY = ‘London’ ;

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

UNIQUE   (   S#   )   ‘

Но, как ни странно, в этом определении можно задать общее ограничение в следующей форме.

CREATE ASSERTION LSK CHECK

( UNIQUE ( SELECT S# FROM LONDON_SUPPLIER ) ) ;

Отложенная проверка

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

языке SQL ограничения22 могут быть определены как допускающие отложенную проверку (DEFERRABLE) или не допускающие такую проверку (NOT DEFERRABLE); если заданное ограничение объявлено как DEFERRABLE, оно может быть дополнительно определено как отложенное  первоначально  (INITIALLY  DEFERRED)  или  немедленно  выполняемое  с самого начала (INITIALLY  IMMEDIATE); эти ключевые слова  определяют состояние ограничения в начале каждой транзакции. Ограничения NOT DEFERRABLE всегда проверяются немедленно, а проверку ограничений DEFERRABLE можно динамически включать и выключать с помощью следующего оператора.

SET  CONSTRAINTS  <constraint name commalist> <option>   ;

Здесь опция  <option> принимает значение IMMEDIATE или DEFERRED.  Пример применения такой опции приведен ниже.

SET CONSTRAINTS SSP5, SSP6 DEFERRED ;

Ограничения  DEFERRABLE  проверяются,  только  если  они  находятся  в  состоянии IMMEDIATE. Перевод ограничения DEFERRABLE в состояние  IMMEDIATE вызывает немедленную проверку этого ограничения; если данная проверка оканчивается неудачей, то и выполнение оператора SET IMMEDIATE оканчивается неудачей. Выполнение оператора COMMIT влечет за собой вызов операторов SET IMMEDIATE для всех ограничений DEFERRABLE; если после этого хотя бы одна из проверок целостности оканчивается неудачей, происходит откат транзакции.

Триггеры

Оператор CREATE TRIGGER языка SQL выглядит следующим образом.

CREATE TRIGGER < trigger name>

<before or after> <event> ON <base table

name> [ REFERENCING <naming commalist> } [

FOR EACH <row or statement> ] [ WHEN (

<bool exp> ) ] <action> ;

Пояснения к этому оператору приведены ниже.

1.  Спецификация с указанием времени проверки до или после активизации триггера,

<before  or after>, принимает значение BEFORE или AFTER (в стандарте SQL не поддерживается ключевое слово INSTEAD  OF, но в некоторых программных продуктах такая поддержка предусмотрена).

2.  Событие <event> может принимать значение INSERT, DELETE или UPDATE. Зна чение UPDATE может дополнительно уточняться с помощью спецификации OF

<column  name    commalist>.

3.  Каждое определение именования <naming> может принимать одну из следующих форм.

22   Но  некоторые  ограничения  должны  быть  обязательно  указаны  как  относящиеся  к  типу  NOT DEFERRABLE. Например, если FK — внешний ключ, то ограничение потенциального ключа для соответствующего потенциального ключа должно быть задано как NOT DEFERRABLE.

OLD    ROW    AS

<name>  NEW  ROW

AS  <name>  OLD

TABLE AS <name>

NEW   TABLE   AS

<name>

4. Спецификация с определением строки или оператора <row   or    statement>

принимает значение ROW или STATEMENT (STATEMENT применяется по умолча нию). Ключевое слово ROW означает, что триггер активизируется для каждой от дельной строки, на которую распространяется действие триггерного оператора, а STATEMENT означает, что триггер активизируется только один раз для данного оператора, рассматриваемого как единое целое.

5.  Если определена конструкция WHEN, она означает, что действие <action> должно выполняться, только если логическое выражение <bool  ехр> принимает значе ние TRUE.

6.  Спецификация <action> задает отдельный оператор SQL (но этот отдельный оператор может быть достаточно сложным, т.е. составным, а это неформально оз начает, что такой оператор может состоять из последовательности операторов, обозначенных разграничителями BEGIN и END).

Наконец, ниже показан синтаксис оператора DROP TRIGGER.

DROP TRIGGER <trigger name> ;

Как и в операторе DROP ASSERTION, в операторе DROP TRIGGER не предусмотрено использование пары противоположных опций RESTRICT И CASCADE.

9.8.      РЕЗЮМЕ

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

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

Если (IF) некоторые кортежи присутствуют в некоторых переменных отношения, то

(THEN) эти кортежи удовлетворяют некоторому условию.

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

Затем было показано, что любое ограничение в том виде, в каком оно сформулировано, представляет собой предикат, а при его проверке (т.е. при подстановке текущих значений отношений вместо переменных отношения, указанных в этом предикате), оно становится высказыванием.  Предикатом   переменной  отношения  для  определенной  переменной отношения  является  логическое  выражение,  состоящее  из  всех  предикатов,  которые применяются  к   данной  переменной  отношения,  соединенных  операторами  "И",  а предикатом базы данных для определенной базы данных является логическое выражение, состоящее из всех предикатов, которые применяются к этой базе данных, соединенных операторами "И". Кроме того, в этой главе сформулировано золотое правило, приведенное ниже.

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

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

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

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

■      Ограничения типа определяют допустимые значения для конкретного типа (или домена) и проверяются во время вызова соответствующего селектора.

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

■ Ограничения переменной отношения задают допустимые значения для конкретной переменной отношения и проверяются при обновлении рассматриваемой переменной отношения.

Ограничения базы данных задают допустимые значения для конкретной базы данных и проверяются при обновлении рассматриваемой базы данных.

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

Затем были описаны такие важные с точки зрения практики частные случаи ограничений,   как   потенциальные,   первичные,   альтернативные   и   внешние   ключи. Потенциальные  ключи обладают свойствами уникальности и  несократимости, причем каждая переменная отношения (без каких-либо исключений!) должна иметь по меньшей мере  один  такой  ключ.  Ограничения,   согласно  которым  значения  определенного внешнего  ключа  должны  совпадать  со  значениями  соответствующего  потенциального ключа,  называются  ограничениями  ссылочной  целостности;  в  этой  главе  описано несколько  областей применения идеи ссылочной целостности, включая, в частности, понятие ссылочных действий (причем наиболее важной из этих областей применения является каскадное распространение действий с помощью ключевого слова CASCADE). По  материалам  обсуждения  этой  последней  темы  было  сделано  краткое  отступление, касающееся темы триггеров.

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

Источник: Дейт К. Дж., Введение в системы баз данных, 8-е издание.: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 1328 с.: ил. — Парал. тит. англ.

По теме:

  • Комментарии