Ограничения целостности. Представления данных

Заявляемые ограничения целостности

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

Само понятие заявляемых ограничений целостности в SQL было унаследовано от реляционной модели и усложнялось вместе с развитием стандарта. В Oracle номенклатура ограничений целостности в целом соответствует SQL-92 (при том, что объем реализации не выдержан), но не доведена до уровня SQL:1999. Так, Oracle не позволяет завести ограничение целостности на уровне БД (с помощью служебного слова ASSERTION) и сильно ограничен в формулировании условия проверки значений конструкцией CHECK тем, что не допускает обращения к данным базы.

Слово ASSERTION из стандарта SQL подсказывает еще один перевод (и понимание) integrity constraints, как "утвердительные ограничения целостности".

Заявляемые ограничения целостности в Oracle можно задавать на уровнях:

• отдельного поля строки в таблице;
• отдельной строки;
• пары таблиц.

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

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

• ALTER TABLE … MODIFY — добавление ограничений всех видов и снятие ограничения NOT NULL;
• ALTER TABLE … ADD/DROP — добавление и снятие ограничений всех видов, кроме NOT NULL.

Всем ограничениям целостности, сформулированными в схеме, Oracle сообщает имена. Если при создании ограничения употребить конструкцию CONSTRAINT имя, ограничение получит имя от программиста, в противном случае СУБД создаст имя по своему усмотрению. Сведения о каждом существующем ограничении можно найти в таблице словаря-справочника USER_CONSTRAINTS по его имени. Неудачное имя ограничения можно изменить; к примеру:

ALTER TABLE projx RENAME CONSTRAINT sys_c0011509 TO name_is_needed;

Разновидности заявляемых ограничений целостности

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

Ограничение NOT NULL обязывает столбец или группу столбцов всегда иметь значение (если группа — то хотя бы в одном поле). Требование непустоты столбца крайне желательно, так как избавляет программиста от многочисленных забот, связанных с особенностями обработки NULL. К сожалению, требования предметной области и некоторые действия в SQL (например, GROUP BY ROLLUP …) не позволяют совсем отказаться от столбцов со свойством NULL.

Это единственное из ограничений целостности, информация о котором хранится не только в таблице USER_CONSTRAINTS, но и в таблице USER_TAB_COLUMNS в качестве свойства столбца. (Когда-то признак NULL/NOT NULL формально считался свойством столбца, а не ограничением целостности). По этой причине добавление и упразднение этого ограничения оформляется по правилам изменения свойства столбца, только через ключевое слово MODIFY:

ALTER TABLE proj MODIFY ( budget NOT NULL );
-- создание ограничения с системным именем; скобки необязательны
ALTER TABLE proj MODIFY ( budget NULL );
-- упразднение ограничения; скобки необязательны 
ALTER TABLE proj MODIFY ( budget CONSTRAINT is_mandatory NOT NULL );
-- создание ограничения с именем, заданным программистом

В современных версиях Oracle самостоятельное ограничение NOT NULL будет оформлено технически как ограничение вида CHECK с условием для проверки: budget IS NOT NULL и одновременно будет зафиксировано в USER_CONSTRAINTS значением NULLABLE = 'Y'. Свойство NOT NULL, вытекающее из правила первичного ключа, будет отражено только в USER_CONSTRAINTS.

Первичные ключи

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

ALTER TABLE proj ADD PRIMARY KEY ( projno, pname );
-- создание ограничения (первичный ключ на основе двух столбцов) с системным именем
ALTER TABLE proj DROP PRIMARY KEY;
-- упразднение ограничения 
ALTER TABLE proj ADD CONSTRAINT pk_proj PRIMARY KEY ( projno );
-- создание ограничения с именем, заданным программистом

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

Некоторые типы столбцов не допускаются до формирования первичного ключа (например, LOB или TIMESTAMP WITH TIME ZONE).

Уникальность значений в столбцах

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

Пример создания:

ALTER TABLE proj ADD UNIQUE ( pname );

Обратите внимание, что в столбце PNAME не запрещаются пропуски значений. По стандарту SQL уникальность отслеживается для имеющихся значений столбца. Если на такой столбец дополнительно наложить ограничение

ALTER TABLE proj MODIFY ( pname NOT NULL );

он сможет играть роль ключа в реляционной модели и быть объявлен первичным (путем замены двух ограничений: UNIQUE и NOT NULL на одно PRIMARY KEY). Если же уникальной объявляется группа столбцов, сообщить ей свойства ключа средствами SQL сложнее (обязательность хотя бы одного значения в уникальной группе можно потребовать ограничением вида CHECK).

Другое отличие ограничения уникальности от первичного ключа в том, что первых в таблице может быть сформулировано несколько, а второе присутствует разве что в единственном числе. Oracle не препятствует объявлению уникальности не только непересекающихся групп столбцов, но даже и повторяющихся. Следующая цепочка команд не вызовет ошибок:

CREATE TABLE t ( a NUMBER, b NUMBER, c NUMBER );
ALTER TABLE t ADD CONSTRAINT ab UNIQUE ( a, b );
ALTER TABLE t ADD CONSTRAINT bc UNIQUE ( b, c );
ALTER TABLE t ADD CONSTRAINT ba UNIQUE ( b, a );

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

ALTER TABLE emp 
  ADD CONSTRAINT no_duplicates 
  UNIQUE ( deptno, job, hiredate )
;

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

Однако точное повторение списка имен столбцов в новом определении приведет к ошибке (что довольно необычно логически и вызвано техническими причинами реализации):

ALTER TABLE t ADD CONSTRAINT xx UNIQUE ( a, b );
-- Ошибка !

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

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

ALTER TABLE proj ADD ( ldept NUMBER ( 2 ) )
;
ALTER TABLE proj ADD FOREIGN KEY ( ldept ) REFERENCES dept ( deptno )
;

По правилам внешнего ключа в столбце LDEPT не запрещаются пропуски значений. Стандарт SQL требует от СУБД проверки соответствия значениям в столбцах-адресатах таблицы только имеющихся значений внешнего ключа; иными словами, значения в полях внешнего ключа могут отсутствовать.

Внешних ключей в таблице может быть определено несколько. Например, при более тщательном моделировании примера "сотрудники — отделы" в дополнение к имеющемуся внешнему ключу DEPTNO таблицы EMP можно было бы объявить внешним ключом столбец JOB, заставив его ссылаться на отдельную таблицу с описаниями штатных должностей.