Used in : Postgre sql
CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.
Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table’s constraint individually.
Examples:
CREATE DOMAIN CPI_DATA AS REAL CHECK (value >= 0 AND value <= 10);
Now CPI_DATA domain is create so, we can use this domain anywhere in any table of database as below :
CREATE TABLE student( sid char(9) PRIMARY KEY, name varchar(30), cpi CPI_DATA );
Every time cpi_data will check the constraint, when you add data in student table.
Example 1 :
Insert into student values (201501408,Raj,7.5); This will not violate the property of cpi.
Example 2 :
Insert into student values (201501188,Dhaval,12); ERROR. This will violate the property of cpi.
This article is contributed by Dhavalkumar Prajapati. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
Last Updated :
07 Sep, 2018
Like Article
Save Article
Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
|
2008 г. Базы данных. Вводный курсСергей Кузнецов Назад Содержание Вперёд 15.3. Средства определения, изменения определения и отмены определения доменовКак неоднократно упоминалось выше, при определении столбцов таблицы требуется явно указывать тип данных каждого столбца. Для этого можно использовать описанные выше средства спецификации типа. Но в SQL поддерживается и другой механизм— механизм доменов. Домен является долговременно хранимым, именованным объектом схемы базы данных. Домены можно создавать (определять), изменять (изменять определения) и ликвидировать (отменять определение). Имена доменов можно использовать при определении столбцов таблиц. Можно считать, что в SQL определение домена представляет собой вынесенное за пределы определения индивидуальной таблицы «родовое» определение столбца, которое можно использовать для определения различных реальных столбцов реальных базовых таблиц. В языке SQL обеспечиваются средства определения доменов, изменения и отмены существующих определений. 15.3.1. Определение доменаДля определения домена в SQL используется оператор domain_definition ::= CREATE DOMAIN domain_name [AS] data_type
[ default_definition ]
[ domain_constraint_definition_list ]
Здесь Раздел
Здесь
Если в операторе Элемент списка [CONSTRAINT constraint_name]
CHECK (conditional_expression)
Необязательный раздел CHECK (VALUE IN (list_of_valid_values)) Такое ограничение запрещает появление в любом столбце, определенном на данном домене, любого значения определяющего типа, не входящего в список допустимых значений. 15.3.2. Примеры определений доменовВ дальнейших примерах нам понадобятся определения нескольких доменов. Приведем их в этом подразделе. В примерах мы будем иметь дело с таблицами служащих ( CREATE DOMAIN EMP_NO AS INTEGER
CHECK (VALUE BETWEEN 1 AND 10000);
Номера служащих являются целыми числами, поэтому базовый тип домена Домен CREATE DOMAIN SALARY AS NUMERIC (10, 2)
DEFAULT 10000.00
CHECK (VALUE BETWEEN 10000.00 AND 20000000.00)
CONSTRAINT SAL_NOT_NULL CHECK (VALUE IS NOT NULL);
Размер заработной платы является значением точного числового типа 15.3.3. Изменение определения доменаДля изменения характеристик ранее определенного домена используется оператор SQL domain_alternation ::=
ALTER DOMAIN domain_name domain_alternation_action
domain_alternation_action ::=
domain_default_alternation_action
| domain_constraint_alternation_action
Как видно из синтаксических правил, при изменении определения домена можно выполнить действие по изменению раздела значения по умолчанию либо изменить ограничение домена. Для первого варианта действует следующий синтаксис: domain_default_alternation_action ::=
SET default_definition
| DROP DEFAULT
В случае установки нового значения по умолчанию ( Действие по изменению ограничения домена определяется следующим синтаксисом: domain_constraint_alternation_action ::=
ADD domain_constraint_definition
| DROP CONSTRAINT constraint_name
Действие по добавлению нового определения ограничения домена ( 15.3.4. Примеры изменения определения доменаНемного поупражняемся с доменом ALTER DOMAIN SALARY SET DEFAULT 11000.00; Для отмены значения по умолчанию в домене ALTER DOMAIN SALARY DROP DEFAULT; Если к определению домена ALTER DOMAIN SALARY ADD CHECK (VALUE <> 15000.00); Наконец, если требуется отменить (именованное!) ограничение целостности, препятствующее наличию неопределенных значений в столбцах, которые определены на домене ALTER DOMAIN SALARY DROP CONSTRAINT SAL_NOT_NULL; 15.3.5. Отмена определения доменаЧтобы отменить ранее созданное определение домена, нужно воспользоваться оператором DROP DOMAIN domain_name {RESTRICT | CASCADES}
Если в операторе указано Если в операторе
15.4. Неявные и явные преобразования типа или доменаВ языке SQL обеспечивается возможность использования в различных операциях не только значений тех типов, для которых предопределена операция, но и значений типов, неявным или явным образом приводимых к требуемому типу. 15.4.1. Неявные преобразования типов в SQLВ SQL поддерживается совместимость некоторых типов данных за счет неявного преобразования значений одного типа к значениям другого типа данных (например, при необходимости Основные правила приводимости типов состоят в следующем.
15.4.2. Явные преобразования типов или доменов и оператор CASTНеявные преобразования типов не всегда удобны, недостаточно гибки и иногда могут вызывать ошибки. Поэтому, как показывает предыдущий подраздел, число допустимых неявных преобразований типов в SQL весьма ограничено. Однако в SQL существует специальный оператор CAST ({scalar-expression | NULL } AS
{data_type | domain_name})
Оператор преобразует значение заданного скалярного выражения к указанному типу или к базовому типу указанного домена. Результатом применения оператора Поясним действие оператора
Пусть
По поводу ячеек таблицы, содержащих знак вопроса, необходимо сделать несколько оговорок:
15.5. ЗаключениеВ этой лекции мы начали рассматривать средства языка SQL, позволяющие определять и динамически изменять схему базы данных. Наиболее важным для общего понимания языка является раздел 15.2. Типы данных SQL – система типов языка SQL (и любой SQL-ориентированной базы данных). В последних стандартах языка SQL поддерживаются:
Нельзя с уверенностью сказать, что система типов языка SQL настолько полна, что может удовлетворить любые потребности, но можно отметить, что в этой системе типов отсутствует единый логический подход и имеется избыточность. Возможно, это станет понятнее после обсуждения в конце курса средств объектно-реляционных расширений языка SQL. Как должно быть ясно из этой лекции, механизм доменов в SQL играет вспомогательную роль. Это не совсем те (может быть, и совсем не те) домены, поддержка которых предполагается реляционной моделью. Фактически определение домена обеспечивает спецификацию ограничений и значений по умолчанию, выносимых за пределы определения столбца. В комитете по стандартизации SQL обсуждается идея полного отказа от поддержки механизма доменов и замены его на соответствующим образом адаптированный механизм индивидуальных типов (см. последнюю лекцию курса). 88 Начиная с этого места мы будем приводить более или менее точный синтаксис конструкций языка SQL (не злоупотребляя излишествами). Без этого текст был бы менее точным и более объемным. Прописными буквами показываются «терминалы» – ключевые слова языка SQL. 89 Здесь мы в первый раз сталкиваемся с именем объекта базы данных. Не будем углублять ся в детали, но в общем случае имена объектов SQL-ориентированных баз данных имеют вид 90 Это значение будет использоваться в качестве значения по умолчанию для любого столбца, определенного на данном домене, для которого не определено собственное значение по умолчанию (см. следующую лекцию). 91 92 Значение 93 Более подробно мы обсудим допустимые в SQL виды условных выражений в следующих лекциях. Назад Содержание Вперёд |
|
x
CREATE DOMAIN — define a new domain
Synopsis
CREATE DOMAINname[ AS ]data_type[ COLLATEcollation] [ DEFAULTexpression] [constraint[ ... ] ] whereconstraintis: [ CONSTRAINTconstraint_name] { NOT NULL | NULL | CHECK (expression) }
Description
CREATE DOMAIN creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner.
If a schema name is given (for example, CREATE DOMAIN myschema.mydomain ...) then the domain is created in the specified schema. Otherwise it is created in the current schema. The domain name must be unique among the types and domains existing in its schema.
Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK constraint to verify the address syntax. Define a domain rather than setting up each table’s constraint individually.
To be able to create a domain, you must have USAGE privilege on the underlying type.
Parameters
name-
The name (optionally schema-qualified) of a domain to be created.
data_type-
The underlying data type of the domain. This can include array specifiers.
collation-
An optional collation for the domain. If no collation is specified, the domain has the same collation behavior as its underlying data type. The underlying type must be collatable if
COLLATEis specified. DEFAULTexpression-
The
DEFAULTclause specifies a default value for columns of the domain data type. The value is any variable-free expression (but subqueries are not allowed). The data type of the default expression must match the data type of the domain. If no default value is specified, then the default value is the null value.The default expression will be used in any insert operation that does not specify a value for the column. If a default value is defined for a particular column, it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.
CONSTRAINTconstraint_name-
An optional name for a constraint. If not specified, the system generates a name.
NOT NULL-
Values of this domain are prevented from being null (but see notes below).
NULL-
Values of this domain are allowed to be null. This is the default.
This clause is only intended for compatibility with nonstandard SQL databases. Its use is discouraged in new applications.
CHECK (expression)-
CHECKclauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the key wordVALUEto refer to the value being tested. Expressions evaluating to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, an error is reported and the value is not allowed to be converted to the domain type.Currently,
CHECKexpressions cannot contain subqueries nor refer to variables other thanVALUE.When a domain has multiple
CHECKconstraints, they will be tested in alphabetical order by name. (PostgreSQL versions before 9.5 did not honor any particular firing order forCHECKconstraints.)
Notes
Domain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join. A more subtle example is
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
The empty scalar sub-SELECT will produce a null value that is considered to be of the domain type, so no further constraint checking is applied to it, and the insertion will succeed.
It is very difficult to avoid such problems, because of SQL’s general assumption that a null value is a valid value of every data type. Best practice therefore is to design a domain’s constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.
PostgreSQL assumes that CHECK constraints’ conditions are immutable, that is, they will always give the same result for the same input value. This assumption is what justifies examining CHECK constraints only when a value is first converted to be of a domain type, and not at other times. (This is essentially the same as the treatment of table CHECK constraints, as described in Section 5.4.1.)
An example of a common way to break this assumption is to reference a user-defined function in a CHECK expression, and then change the behavior of that function. PostgreSQL does not disallow that, but it will not notice if there are stored values of the domain type that now violate the CHECK constraint. That would cause a subsequent database dump and restore to fail. The recommended way to handle such a change is to drop the constraint (using ALTER DOMAIN), adjust the function definition, and re-add the constraint, thereby rechecking it against stored data.
It’s also good practice to ensure that domain CHECK expressions will not throw errors.
Examples
This example creates the us_postal_code data type and then uses the type in a table definition. A regular expression test is used to verify that the value looks like a valid US postal code:
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL PRIMARY KEY,
street1 TEXT NOT NULL,
street2 TEXT,
street3 TEXT,
city TEXT NOT NULL,
postal us_postal_code NOT NULL
);
Compatibility
The command CREATE DOMAIN conforms to the SQL standard.
Средства определения, изменения определения и отмены определения доменов
Как неоднократно упоминалось выше, при определении столбцов таблицы требуется явно указывать тип данных каждого столбца. Для этого можно использовать описанные выше средства спецификации типа. Но в SQL поддерживается и другой механизм— механизм доменов. Домен является долговременно хранимым, именованным объектом схемы базы данных. Домены можно создавать (определять), изменять (изменять определения) и ликвидировать (отменять определение). Имена доменов можно использовать при определении столбцов таблиц. Можно считать, что в SQL определение домена представляет собой вынесенное за пределы определения индивидуальной таблицы «родовое» определение столбца, которое можно использовать для определения различных реальных столбцов реальных базовых таблиц. В языке SQL обеспечиваются средства определения доменов, изменения и отмены существующих определений.
Определение домена
Для определения домена в SQL используется оператор CREATE DOMAIN . Общий синтаксис этого оператора следующий: 22Начиная с этого места мы будем приводить более или менее точный синтаксис конструкций языка SQL (не злоупотребляя излишествами). Без этого текст был бы менее точным и более объемным. Прописными буквами показываются «терминалы» – ключевые слова языка SQL .
domain_definition ::= CREATE DOMAIN domain_name [AS] data_type
[ default_definition ]
[ domain_constraint_definition_list ]
Здесь domain_name задает имя создаваемого домена 23Здесь мы в первый раз сталкиваемся с именем объекта базы данных. Не будем углублять ся в детали, но в общем случае имена объектов SQL -ориентированных баз данных имеют вид имя_каталога.имя_схемы.имя_объекта. Этот подход к именованию объектов базы данных позволяет независимо создавать объекты в разных схемах, не заботясь о том, чтобы эти объекты имели разные простые имена. При использовании в операторе SQL простого имени объекта система должна автоматически уточнить это имя, исходя из идентификатора пользователя, от имени которого выполняется оператор., data_type есть спецификация определяющего типа данных. В необязательных разделах default_definition и domain_constraint_definition_list специфицируются значение домена по умолчанию 24Это значение будет использоваться в качестве значения по умолчанию для любого столбца, определенного на данном домене, для которого не определено собственное значение по умолчанию (см. следующую лекцию). и набор ограничений целостности, которые будут применяться к любому столбцу, определенному на этом домене.
Раздел default_definition имеет вид
DEFAULT { literal | niladic_function | NULL } 25 { element1, | element2, |…| elementn } означает, что в данной синтаксической конструкции должен присутствовать один и только один elementi .
Здесь literal представляет любое допустимое литеральное значение определяющего типа домена, NULL обозначает неопределенное значение, а niladic_function может задаваться в одной из следующих форм:
USER
CURRENT_USER
SESSION_USER
SYSTEM_USER
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP 26Значение niladic_function «вычисляется» в тот момент, когда требуется значение по умолчанию (обычно при вставке в таблицу новой строки, значение соответствующего столбца которой явно не указано). Смысл CURRENT_DATE, CURRENT_TIME и CURRENT_TIMESTAMP очевиден. USER (или, что то же, CURRENT USER ), SESSION_USER и SYSTEM_USER задают идентификатор пользователя, от имени которого выполняется текущая транзакция, текущая сессия, и идентификатор операционной системы, в которой работает пользователь, соответственно. В стандарте не определяется представление этих идентификаторов, но в реализациях они обычно представляются в виде символьных строк .
Если в операторе CREATE DOMAIN значение по умолчанию не специфицируется, считается, что такого значения нет. Однако позже к определению домена можно добавить раздел значения по умолчанию с помощью оператора ALTER DOMAIN . Кроме того, этот оператор позволяет удалить раздел значения по умолчанию из существующего определения домена.
Элемент списка domain_constraint_definition_list имеет вид
[CONSTRAINT constraint_name]
CHECK (conditional_expression)
Необязательный раздел CONSTRAINT constraint_name позволяет определить имя нового ограничения целостности. Если явное указание имени отсутствует, ограничению назначается имя, автоматически генерируемое системой. Что касается вида условного выражения, служащего собственно ограничением целостности, то в стандарте запрещается лишь прямое или косвенное использование в нем домена, в определение которого входит данное условное выражение. 27Более подробно мы обсудим допустимые в SQL виды условных выражений в следующих лекциях. Однако наиболее естественным (и наиболее распространенным) видом ограничения домена является следующий:
CHECK (VALUE IN (list_of_valid_values))
Такое ограничение запрещает появление в любом столбце, определенном на данном домене, любого значения определяющего типа, не входящего в список допустимых значений.
Примеры определений доменов
В дальнейших примерах нам понадобятся определения нескольких доменов. Приведем их в этом подразделе. В примерах мы будем иметь дело с таблицами служащих ( EMP ), отделов ( DEPT ) и проектов ( PRO ). Каждый служащий обладает уникальным номером ( EMP_NO ) и получает заработную плату ( SALARY ). Определим домены EMP_NO и SALARY.
CREATE DOMAIN EMP_NO AS INTEGER
CHECK (VALUE BETWEEN 1 AND 10000);
Номера служащих являются целыми числами, поэтому базовый тип домена EMP_NO есть тип INTEGER. Кроме того, на значения этого домена устанавливается следующее ограничение: они должны быть больше нуля и не превосходить целое значение 10000.
Домен SALARY определим следующим образом:
CREATE DOMAIN SALARY AS NUMERIC (10, 2)
DEFAULT 10000.00
CHECK (VALUE BETWEEN 10000.00 AND 20000000.00)
CONSTRAINT SAL_NOT_NULL CHECK (VALUE IS NOT NULL);
Размер заработной платы является значением точного числового типа NUMERIC из десяти десятичных цифр, две из которых составляют дробную часть. По умолчанию размер заработной платы составляет 10000 руб. Установлен диапазон допустимого размера зарплаты от 10000 руб. до 20000000 руб. Неопределенное значение зарплаты не допускается (на уровне определения домена ).
Изменение определения домена
Для изменения характеристик ранее определенного домена используется оператор SQL ALTER DOMAIN . Синтаксис этого оператора выглядит следующим образом:
domain_alternation ::=
ALTER DOMAIN domain_name domain_alternation_action
domain_alternation_action ::=
domain_default_alternation_action
| domain_constraint_alternation_action
Как видно из синтаксических правил, при изменении определения домена можно выполнить действие по изменению раздела значения по умолчанию либо изменить ограничение домена. Для первого варианта действует следующий синтаксис:
domain_default_alternation_action ::=
SET default_definition
| DROP DEFAULT
В случае установки нового значения по умолчанию ( SET ) это значение автоматически применяется ко всем столбцам, определенным на данном домене. Более точно, это значение становится новым значением по умолчанию. Операция не оказывает влияния на состояние существующих строк таблиц базы данных. В случае отмены раздела значения по умолчанию в определении домена ( DROP ) существовашее значение домена по умолчанию становится значением по умолчанию каждого столбца, который определен на данном домене и для которого не специфицировано собственное значение по умолчанию.
Действие по изменению ограничения домена определяется следующим синтаксисом:
domain_constraint_alternation_action ::=
ADD domain_constraint_definition
| DROP CONSTRAINT constraint_name
Действие по добавлению нового определения ограничения домена ( ADD ) приводит к тому, что новое условие добавляется через AND к существующему ограничению домена. Если к моменту выполнения соответствующего оператора ALTER DOMAIN существуют столбцы некоторых таблиц, текущие значения которых противоречат новому ограничению, то СУБД должна отвергнуть этот оператор ALTER DOMAIN . Действие по отмене ограничения домена ( DROP ) приводит к исчезновению соответствующей части общего ограничения соответствующего домена, что, естественно, не влияет на существующие значения столбцов имеющихся таблиц.
Примеры изменения определения домена
Немного поупражняемся с доменом SALARY. Для изменения значения заработной платы по умолчанию с 10000 на 11000 руб. нужно выполнить оператор
ALTER DOMAIN SALARY SET DEFAULT 11000.00;
Для отмены значения по умолчанию в домене SALARY следует воспользоваться оператором
ALTER DOMAIN SALARY DROP DEFAULT;
Если к определению домена SALARY требуется добавить ограничение (например, запретить значение зарплаты, равное 15000 руб.), необходимо выполнить оператор
ALTER DOMAIN SALARY ADD CHECK (VALUE <> 15000.00);
Наконец, если требуется отменить (именованное!) ограничение целостности, препятствующее наличию неопределенных значений в столбцах, которые определены на домене SALARY, то нужно выполнить оператор
ALTER DOMAIN SALARY DROP CONSTRAINT SAL_NOT_NULL;
Отмена определения домена
Чтобы отменить ранее созданное определение домена, нужно воспользоваться оператором DROP DOMAIN в следующем синтаксисе:
DROP DOMAIN domain_name {RESTRICT | CASCADES}
Если в операторе указано RESTRICT, и если соответствующий домен использован в определении некоторого столбца, в определении некоторого представления или в определении ограничения целостности (см. следующие лекции), то оператор DROP DOMAIN отвергается. В противном случае определение домена ликвидируется.
Если в операторе DROP DOMAIN указано CASCADES, то оператор выполняется всегда. При этом уничтожаются все представления и ограничения целостности, в определении которых использовалось имя данного домена. Столбцы, определенные на этом домене, автоматически переопределяются следующим образом:
- считается, что каждый такой столбец теперь относится к определяющему типу уничтожаемого домена;
- если у столбца не было определено собственное значение по умолчанию, то считается, что теперь у него имеется такое значение по умолчанию, совпадающее со значением по умолчанию уничтожаемого домена;
- каждый столбец наследует все ограничения уничтожаемого домена.