Ограничения

На поля таблицы могут быть наложены ограничения – специальные требования, или условия, которые ограничивают множество значений, которые могут быть присвоены полям таблицы. Эти условия обычно используются для того, чтобы запретить помещать в базу данных значения, которые являются логически неправильными или противоречивыми. Например, поле “Возраст”, объявленное как целочисленное, не может содержать отрицательные значения, или, например, значение менее 18, и т.д. В целом, ограничения отражают семантические свойства данных предметной области, и выявляются при ее анализе. Использовать ограничения не обязательно, более того, их определение в схеме базы данных потенциально снижает скорость работы системы, однако их использование эффективно предотвращает накапливание в БД неправильных данных и откровенного “мусора” и повышает качество и эксплуатационные свойства системы.

Проверка выполнения ограничения производится при выполнении SQL-сервером команд обновления данных (INSERT, UPDATE и DELETE), а так же при изменении структуры таблицы. Если ограничение при конкретном обновлении не выполняется, генерируется ошибка.

Объявление ограничения в команде CREATE TABLE может помещаться непосредственно после определения поля (эти ограничения так же называются ограничениями на уровне полей, или in-line-ограничениями), или отдельным описанием (в этом случае их называют ограничениями на уровне таблицы, или out-line-ограничениями). Для одного поля может быть определено несколько in-line-ограничений. Ограничение на уровне таблицы обычно используют для накладывания ограничения на несколько полей, а ограничение на уровне поля затрагивает, естественно, только одно это поле.

Полное определение ограничения имеет следующий синтаксис:

CONSTRAINT имя_ограничения описание_ограничения

Здесь имя_ограничения - имя, которое присваивается данному ограничению (должно быть уникальными среди всех использованных имен ограничений в данной схеме); явное присваивание имени ограничению полезно для дальнейших действий с этим ограничением. Для in-line-ограничений служебное слово CONSTRAINT и имя ограничения необязательны и могут быть пропущены, при этом имя для ограничения создается автоматически.

Ограничения в SQL Oracle бывают следующих видов:

1. Ограничение NOT NULL.

2. Ограничение первичного ключа PRIMARY KEY.

3. Ограничение доменной целостности CHECK.

4. Ограничение уникальности UNIQUE.

5. Ограничение референциальной целостности FOREIGN KEY.

Если на поле наложено ограничение NOT NULL, это означает, что в данном поле любой записи не может храниться значение NULL. Данное ограничение – единственное, которое может быть наложено на поля “длинных” типов BLOB и CLOB.

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

Ограничение уникальности UNIQUE используется для того, чтобы в данном поле значения во всех записях были различными, или, иными словами, в таблице не должно существовать две записи с одинаковыми значениями этого поля. Это ограничение может быть наложено на одно поле или на группу полей; однако нельзя на одни и те же поля накладывать ограничение UNIQUE дважды.

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

Частными случаем ограничения уникальности является ограничение первичного ключа PRIMARY KEY, которое требует дополнительно отсутствие пустых значений в группе полей. В дополнении к этому, ограничение PRIMARY KEY может встречаться только один раз среди ограничений на таблицу; впрочем, эквивалентная комбинация NOT NULL UNIQUE может быть наложена на любые другие поля.

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

Ограничение доменной целостности позволяет контролировать присваиваемое значение данному полю при помощи логического выражения, которое проверяется при присваивании значения и, в случае если значение равно FALSE, ограничение считается нарушенным и возбуждается ошибка. В этом выражении можно использовать различные операции, содержащие присваиваемое значение поля и значения других полей данной записи, если ограничение объявлено как out-of-line. Подзапросы, однако, использовать нельзя, что означает, что при проверке нельзя анализировать данные из других строк этой же таблицы или других таблиц.

Наконец, последним из рассматриваемых ограничений является ограничение внешнего ключа, или ограничение референциальной целостности. Данное ограничение требует, чтобы в качестве значений поля или группы полей можно было использовать только значения полей какой-либо таблицы. Таблицу, на которую ссылается ограничение, называют часто master-таблицей, или родительской, а таблицу, в которой это ограничение определено – slave-таблицей, или подчиненной. Описание ограничений FOREIGN KEY, соответственно, содержит имя родительской таблицы и список ее полей, причем в родительской таблице на эту группу полей должно существовать наложенное ограничение UNIQUE или PRIMARY KEY.

Наконец, пре определении ограничения референциальной целостности можно определить поведение системы при удалении или изменении полей, на которые ссылается вешний ключ. Действительно, что должно происходить, если удалить запись в master-таблице, на которую ссылаются записи в подчиненной таблице? Данный вопрос регламентируется следующим образом. По умолчанию изменения полей референциального ключа и удаление содержащей его записи запрещено; однако существует две дополнительных опции – SET NULL для принудительного присваивания полям внешнего ключа значений NULL при удалении “родительской” записи, и опция CASCADE для удаления “подчиненных” записей. Перенос изменений от “родительских” записей к “подчиненным” называется каскадным обновлением.

Теперь рассмотрим пример определения схемы некоторой учебной базы данных, иллюстрирующей практическое применение ограничений. Используемая схема типична для многочисленных приложений баз данных, например, для Internet-магазинов. Этот пример так же иллюстрирует проектирование баз данных, используемая здесь методология рекомендуется для применения в аналогичных сферах применения.

База данных состоит из четырех таблиц: таблица "Клиент" содержит данные о зарегистрированных клиентах, таблица "Товар" – описание и характеристики продаваемого товара, Заказ" – оформленные заказы пользователей, и, наконец, "СоставЗак" – единицы товаров и их количество в конкретном заказе.

Вставить маленький кусок про ER-диаграммы и диаграмму для этой схемы

1 CREATE TABLE "Клиент"

2 (

3 "Имя" VARCHAR(50) NOT NULL,

4 "Фамилия" VARCHAR(50) NOT NULL,

5 CONSTRAINT "Клиент_Ф_С"

6 UNIQUE ("Имя", "Фамилия"),

7 "Пол" NCHAR

8 CHECK ("Пол" IN ('М','Ж','м','ж')),

9 "Дом Адрес" VARCHAR(100),

10 "Дата Рождения" DATE,

11 "Дом Телефон" CHAR(20),

12 "Моб Телефон" CHAR(20),

13 CONSTRAINT "Клиент_ТТ_С"

14 CHECK ("Дом Телефон" IS NOT NULL

15 OR "Моб Телефон" IS NOT NULL),

16 "Код Клиента" CHAR(20) NOT NULL UNIQUE

17)

18 CREATE TABLE "Товар"

19 (

20 "Код товара" CHAR(20) NOT NULL UNIQUE,

21 "Название" VARCHAR(50) NOT NULL,

22 "Полное название" VARCHAR(200) NOT NULL,

23 "Цена" NUMBER NOT NULL

24 CHECK ("Цена" > 0),

25 "Мера изм" VARCHAR(20)

26 CHECK ("Мера изм"

27 IN ('шт', 'кг', 'литр')),

28 "Наличие" INT CHECK ("Наличие" > 0),

29 "Описание" CLOB,

30 "Фото" BLOB

31)

32 CREATE TABLE "Заказ"

33 (

34 "Код заказа" CHAR(40) PRIMARY KEY,

35 "Дата регистр" DATE DEFAULT current_date

36 NOT NULL,

37 "Клиент" CHAR(20) NOT NULL

38 REFERENCES "Клиент" ("Код Клиента"),

39 CONSTRAINT "Заказ_ДК_U"

40 UNIQUE ("Дата регистр", "Клиент"),

41 "Дата выполн" DATE

42 DEFAULT current_date + 3 NOT NULL,

43 CONSTRAINT "Заказ_Д_C"

44 CHECK("Дата выполн" > "Дата регистр")

45)

46 CREATE TABLE "СоставЗак"

47 (

48 "Код заказа" CHAR(40) NOT NULL,

49 CONSTRAINT "СоставЗак_К_F" FOREIGN KEY

50 ("Код заказа") REFERENCES "Заказ",

51 "Код товара" CHAR(20) NOT NULL,

52 CONSTRAINT "СоставЗак_КК_U"

53 UNIQUE ("Код заказа", "Код товара"),

54 "Количество" INT CHECK ("Количество">0),

55 CONSTRAINT "ТоварВЗаказе_К_C"

56 FOREIGN KEY ("Код товара")

57 REFERENCES "Товар"("Код товара")

58)

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

Строки 3 и 4: наложены два In-line-ограничений NOT NULL, запрещающие создавать клиентов без имени и фамилии.

Строки 5 и 6: out-of-line-ограничение уникальности, которое запрещает повторно создавать клиентов с теми же именами и фамилиями. Определение ограничения содержит явно задаваемое имя ограничения, которое рекомендуется создавать по каким-либо понятным правилам.

Строка 8: ограничение доменной целостности, которое требует, чтобы в данном поле можно было хранить только эти значения пола клиента. Поверка реализована при помощи предиката IN.

Строки 9-12: никакие ограничения не наложены, поскольку эти данные о клиенте необязательны и могут отсутствовать, и их уникальность так же не предполагается.

Строки 13-15. Здесь CHECK-ограничение используется для контроля, что бы хоть один из телефонов был известен (одно из полей не должно хранить пустое значение).

Строка 16. На поле "Код Клиента" наложено одновременно ограничение уникальности и NOT NULL. Предполагается, что в этом поле хранится некоторый системный ключ (такие значения называются суррогатными), однако это поле не объявлено как первичный ключ, чтобы не привлекать к нему излишнего внимания.

Строка 24. Цена не может быть отрицательной.

Строки 26 и 27. Ограничение определяет значения - допустимые единицы измерения товара.

Строки 29, 30. Эти поля предполагается использовать для хранения развернутого описания товара и картинки с его изображением; эти данные не является в данном случае обязательным. Все прочие поля этой таблицы должны быть заполнены.

Строка 34. Наложено ограничение первичного ключа.

Строка 35. Здесь в качестве значения по умолчанию используется текущее время, возвращаемое системной функцией CURRENT_DATE, что удобно для практических целей.

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

Строки 39 и 40. Данное ограничение имеет важный с прикладной точки зрения и не вполне очевидный (с точки зрения анализа SQL-кода) смысл, - оно запрещает создавать для каждого клиента более одного заказа с одинаковыми значениями дат (то есть одновременно). Читателю в качестве упражнения рекомендуется изменить выражение для этого ограничения, чтобы пользователю было запрещено создавать повторные заказы в течение одного дня.

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

Строки 49, 50. Out-of-line-ограничение внешнего ключа, в котором необходимо после ключевого слова FOREIGN KEY указать список полей, к котором это ограничение относится, а затем, после слова REFERENCES, имя родительской таблицы и список ее полей. В этом примере, однако, список полей в родительской таблице опущен, поскольку по умолчанию используется ее первичный ключ (он был явно определен).

Сроки 42, 53. Это ограничение, как читатель уже догадался, запрещает помещать в заказ один и тот же товар более одного раза (следует указывать количество единиц товара в заказе).

Из примера видно, что основное назначение ограничений внешнего ключа – запрещение хранить “бесхозные” записи подчиненных таблиц. Без ограничения в строках 41-42 вполне возможна ситуация, когда некая запись о заказе из одноименной таблицы была бы удалена, а связанные с ней записи в таблице "СоставЗак" остались бы как следствие человеческой ошибки. Это привело бы к известным проблемам при, например, формировании отчетности на основании записей из таблицы "СоставЗак".

Другой любопытной тонкостью является использование ограничения CHECK на строке 23. Как уже говорилось, оно определяет список допустимых единиц измерения. Альтернативными способом реализации данного требования бизнес-логики является создание отдельной таблицы, в каждой строке которое хранится одна из единиц; такие таблицы исторически получили название справочников. Этот способ является более громоздким, но потенциально более гибким, поскольку упрощает, в данном примере, добавление при необходимости новых единиц, и более приемлемым, если количество значений большое. В целом, применение тех или других средств зависит от конкретных целей и ограничений при проектирования БД.