Домены

Создание домена

Для создания нового домена в базе данных используется оператор CREATE DOMAIN. Синтаксис оператора:

CREATE DOMAIN <имя домена> [AS] <тип данных>
    [DEFAULT {<литерал> | NULL | <контекстная переменная>}]
    [NOT NULL]
    [CHECK (<условие домена>)]
    [COLLATE <порядок сортировки>];

Имя домена должно быть уникальным среди имен доменов базы данных. Имя не может превышать 31 символа. Создавать домен может администратор и пользователь с привилегией CREATE DOMAIN.

Предложение DEFAULT задает значение по умолчанию — что должно быть помещено в столбец таблицы, основанный на этом домене, если пользователь в операторе INSERT, добавляющем данные в таблицу, не укажет значение для этого столбца. Значение по умолчанию не используется при выполнении оператора изменения данных в таблице UPDATE. Если в этом операторе пользователь не укажет значение для конкретного столбца, то это значение просто не изменяется.

Значением по умолчанию может быть литерал или пустое значение NULL. Литералом может быть любая самоопределенная константа соответствующего типа, предварительно определенный литерал или контекстная переменная. Если значение по умолчанию не устанавливается, то подразумевается пустое значение NULL. В значении по умолчанию нельзя задавать выражения.

Предложение NOT NULL указывает, что столбцу, основанному на этом домене, не может присваиваться пустое значение ни в операторе INSERT, ни в операторе UPDATE. Это предложение является обязательным, если домен будет использован для создания столбца, входящего в состав первичного ключа таблицы.

Ограничения домена

Предложение CHECK, являясь ограничением домена, задает некоторое условие, которому должно удовлетворять значение, помещаемое поле, основанные на этом домене. Предложение неприменимо к доменам типа BLOB.

Условие в предложении CHECK также иногда называется предикатом. Это логическое выражение, которое может возвращать значения TRUE (истина), FALSE (ложь) и UNKNOWN (неопределенное, неизвестное значение). Условие считается выполненным, если этот предикат возвращает значение TRUE.

Условие домена может быть достаточно сложным. Полный его синтаксис подробно описан в документации к СУБД. Рассмотрим примеры:

CHECK (VALUE >= 18)

Значениям такого домена можно присваивать только числа, которые не меньше 18.

CHECK (SUBSTRING(VALUE FROM 1 FOR 1) = SUBSTRING(VALUE FROM 2 FOR 1))

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

CHECK ((VALUE IS NULL) OR (VALUE BETWEEN '0' AND '9'))

Такое ограничение разрешает использовать или только символьные представления цифр или значение NULL.

CHECK (VALUE IN ('0', '1'))

Так можно имитировать булевское значение.

CREATE DOMAIN D_SELECT AS CHAR(3)
    CHECK(VALUE IN (SELECT CODCOUNTRY FROM COUNTRY))

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

CHECK (UPPER(VALUE) LIKE '%МИР%')

Такое ограничение требует наличия слова “МИР” в присваиваемом значении, причем регистр не имеет значения.

CHECK (VALUE > ALL (SELECT BIRTHDAY FROM PEOPLE))

Данное ограничение будет проверят что каждое новое значение домена больше каждого значения поля BIRTHDAY таблицы PEOPLE.

CHECK (VALUE = ANY (SELECT CODCOUNTRY FROM COUNTRY))

Данное ограничение проверит что присваиваемое значение домена совпадает хотя бы с одним значением поля CODCOUNTRY таблицы COUNTRY.

CHECK (EXISTS (SELECT CODCOUNTRY FROM COUNTRY WHERE CODCOUNTRY = VALUE))

В данном примере обратите внимание на использование ключевого слова VALUE внутри запроса в условии CHECK. Ограничение требует наличия записей в таблице COUNTRY где значение поля CODCOUNTRY равен присваиваемому значению.

CHECK (SINGULAR(SELECT CODCOUNTRY FROM COUNTRY WHERE CODCOUNTRY = VALUE))

Это условие практически аналогично предыдущему, но требует чтобы такая строка была ровно одна.

Как видно из примеров, одну и ту же задачу можно решить с помощью разных синтаксических конструкций и выражений.

Изменение домена

Для изменения характеристик существующего в базе данных домена используется оператор ALTER DOMAIN. Синтаксис оператора:

ALTER DOMAIN <имя>
    [TO <новое имя>]
    [{SET DEFAULT {<литерал> | NULL | <контекстная переменная>}
    | DROP DEFAULT}]
    [{SET | DROP} NOT NULL]
    [{ADD [CONSTRAINT] CHECK (<условие домена>)
    | DROP CONSTRAINT}]
    [TYPE <тип данных>];

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

Предложение SET DEFAULT позволяет установить новое значение по умолчанию.

Предложение DROP DEFAULT удаляет существующее значение по умолчанию. Значением по умолчанию в этом случае неявно становится пустое значение.

Предложение ADD [CONSTRAINT] CHECK добавляет условие домена. Если у домена уже существует условие, то вначале его нужно удалить при помощи предложения DROP CONSTRAINT иначе будет ошибка.

Предложение DROP CONSTRAINT удаляет существующее ограничение CHECK домена. Если домен не содержит ограничения с таким именем, то выполнение подобного оператора не вызовет сообщения об ошибке.

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

Предложение SET NOT NULL устанавливает ограничение NOT NULL для домена. В этом случае для переменных и столбцах базирующихся на домене значение NULL не допускается. Предложение DROP NOT NULL удаляет ограничение NOT NULL для домена.

Изменить существующий домен может владелец домена (его создатель), пользователь с привилегиями администратора или пользователь с привилегией ALTER ANY DOMAIN.

Например,

ALTER DOMAIN D099
    DROP DEFAULT
    SET DEFAULT CURRENT_USER
    DROP CONSTRAINT
    ADD CONSTRAINT
    CHECK (SUBSTRING(UPPER(VALUE) FROM 1 FOR 1) =
            SUBSTRING(UPPER(VALUE) FROM 2 FOR 1));

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

Удаление домена

Для удаления домена используется оператор DROP DOMAIN. Синтаксис оператора удаления домена:

DROP DOMAIN DROP DOMAIN <имя домена>;

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

Удалить существующий домен может владелец домена (его создатель), пользователь с административными привилегиями или пользователь с привилегией DROP ANY DOMAIN.

Например, чтобы удалить домен CODCOUNTRY, нужно выполнить оператор:

DROP DOMAIN CODCOUNTRY;

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