Nullable oracle что это

7

Основные положения

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

Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , > , like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .

Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.

Для удобства сделаем процедуру, печатающую состояние булевого параметра:

и включим опцию печати сообщений на консоль:

Привычные операторы сравнения пасуют перед NULLом:

Сравнение с NULLом

Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.

Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

DECODE идёт против системы:

Пример с составными индексами находится в параграфе про индексы.

Логические операции и NULL

Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.

В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :

Отрицание неизвестности даёт неизвестность:

Оператор OR :

Оператор AND :

Операторы IN и NOT IN

Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL

Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.

Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :

Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката «A»=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО . Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

Попробуем теперь с NOT IN :

Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :

Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

NULL и пустая строка

Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

NULL и пустая строка эквивалентны:

непременный атрибут жёсткого спора:

Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF , обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF . Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.

Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length(») вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = » вернёт состояние НЕИЗВЕСТНО , так как, по сути, эквивалентно val = NULL .

Длина пустой строки не определена:

Сравнение с пустой строкой невозможно:

Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом » (пустая строка), но не позволяют сохранить элемент с индексом NULL:

Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.

Математика NULLа

Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.

Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.

Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:

С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.

NULL и агрегатные функции

Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM , которой необходимо просуммировать ряд (1, 3, null, 2) . Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null .
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…

Таблица с данными. Используется ниже много раз:

Пустые значения игнорируются агрегатами:

Функция подсчёта количества строк COUNT , если используется в виде COUNT(*) или COUNT(константа) , будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение) , то пустые значения будут игнорироваться.

с константой:

С выражением:

Также, следует быть осторожным с функциями вроде AVG . Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3 , а не (1+3+2)/4 . Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL :

Агрегатные функции возвращают состояние НЕИЗВЕСТНО , если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение) . Они в перечисленных выше случаях вернут ноль.

Набор данных только из NULLов:

Пустой набор данных:

NULL в OLAP

Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.

В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.

Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:

Nullable oracle что это

Use a constraint to define an integrity constraint— a rule that restricts the values in a database. Oracle Database lets you create six types of constraints and lets you declare them in two ways.

The six types of integrity constraint are described briefly here and more fully in «Semantics» :

A NOT NULL constraint prohibits a database value from being null.

A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. It prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

A foreign key constraint requires values in one table to match values in another table.

A check constraint requires a value in the database to comply with a specified condition.

A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

You can define constraints syntactically in two ways:

As part of the definition of an individual column or attribute. This is called inline specification.

As part of the table definition. This is called out-of-line specification.

NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

Constraint clauses can appear in the following statements:

Oracle Database does not enforce view constraints. However, you can enforce constraints on views through constraints on base tables.

You can specify only unique, primary key, and foreign key constraints on views, and they are supported only in DISABLE NOVALIDATE mode. You cannot define view constraints on attributes of an object column.

View Constraints for additional information on view constraints and «DISABLE Clause» for information on DISABLE NOVALIDATE mode

External Table Constraints

You can specify only NOT NULL , unique, primary key, and foreign key constraints on external tables. Unique, primary key, and foreign key constraints are supported only in RELY DISABLE mode.

DISABLE Clause for information on RELY and DISABLE .

You must have the privileges necessary to issue the statement in which you are defining the constraint.

To create a foreign key constraint, in addition, the parent table or view must be in your own schema or you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.

( global_partitioned_index::= , local_partitioned_index::= —part of CREATE INDEX , index_attributes::= . The INDEXTYPE IS . clause is not valid when defining a constraint.)

This section describes the semantics of constraint . For additional information, refer to the SQL statement in which you define or redefine a constraint for a table or view.

Oracle Database does not support constraints on columns or attributes whose type is a user-defined object, nested table, VARRAY , REF , or LOB, with two exceptions:

NOT NULL constraints are supported for a column or attribute whose type is user-defined object, VARRAY , REF , or LOB.

NOT NULL , foreign key, and REF constraints are supported on a column of type REF .

Specify a name for the constraint. The name must satisfy the requirements listed in «Database Object Naming Rules» . If you omit this identifier, then Oracle Database generates a name with the form SYS_C n . Oracle stores the name and the definition of the integrity constraint in the USER_ , ALL_ , and DBA_CONSTRAINTS data dictionary views (in the CONSTRAINT_NAME and SEARCH_CONDITION columns, respectively).

Oracle Database Reference for information on the data dictionary views

NOT NULL Constraints

A NOT NULL constraint prohibits a column from containing nulls. The NULL keyword by itself does not actually define an integrity constraint, but you can specify it to explicitly permit a column to contain nulls. You must define NOT NULL and NULL using inline specification. If you specify neither NOT NULL nor NULL , then the default is NULL .

NOT NULL constraints are the only constraints you can specify inline on XMLType and VARRAY columns.

To satisfy a NOT NULL constraint, every row in the table must contain a value for the column.

Oracle Database does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for at least one of the index key columns or create a bitmap index.

Restrictions on NOT NULL Constraints

NOT NULL constraints are subject to the following restrictions:

You cannot specify NULL or NOT NULL in a view constraint.

You cannot specify NULL or NOT NULL for an attribute of an object. Instead, use a CHECK constraint with the IS [ NOT ] NULL condition.

A unique constraint designates a column as a unique key. A composite unique key designates a combination of columns as the unique key. When you define a unique constraint inline, you need only the UNIQUE keyword. When you define a unique constraint out of line, you must also specify one or more columns. You must define a composite unique key out of line.

To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.

Unique constraints are sensitive to declared collations of their key columns. See Collation Sensitivity of Constraints for more details.

When you specify a unique constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, then Oracle recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. You can also use the CREATE UNIQUE INDEX statement to create a unique function-based index that defines a conditional unique constraint. See «Using a Function-based Index to Define Conditional Uniqueness: Example» for more information.

When you specify an enabled unique constraint on an extended data type column, you may receive a «maximum key length exceeded» error when Oracle tries to create the index to enforce uniqueness for the enabled constraint. See «Creating an Index on an Extended Data Type Column» for information on how to work around this issue.

Restrictions on Unique Constraints

Unique constraints are subject to the following restrictions:

None of the columns in the unique key can be of LOB, LONG , LONG RAW , VARRAY , NESTED TABLE , OBJECT , REF , TIMESTAMP WITH TIME ZONE, or user-defined type. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE .

A composite unique key cannot have more than 32 columns.

You cannot designate the same column or combination of columns as both a primary key and a unique key.

You cannot specify a unique key when creating a subview in an inheritance hierarchy. The unique key can be specified only for the top-level (root) view.

When you specify a unique constraint for an external table, you must specify the RELY and DISABLE constraint states. See External Table Constraints for more information.

Primary Key Constraints

A primary key constraint designates a column as the primary key of a table or view. A composite primary key designates a combination of columns as the primary key. When you define a primary key constraint inline, you need only the PRIMARY KEY keywords. When you define a primary key constraint out of line, you must also specify one or more columns. You must define a composite primary key out of line.

To satisfy a primary key constraint:

No primary key value can appear in more than one row in the table.

No column that is part of the primary key can contain a null.

When you create a primary key constraint:

Oracle Database uses an existing index if it contains a unique set of values before enforcing the primary key constraint. The existing index can be defined as unique or nonunique. When a DML operation is performed, the primary key constraint is enforced using this existing index.

If no existing index can be used, then Oracle Database generates a unique index.

When you drop a primary key constraint:

If the primary key was created using an existing index, then the index is not dropped.

If the primary key was created using a system-generated index, then the index is dropped.

When you designate an extended data type column as an enabled primary key, you may receive a «maximum key length exceeded» error when Oracle tries to create the index to enforce uniqueness for the enabled constraint. See «Creating an Index on an Extended Data Type Column» for information on how to work around this issue.

Primary key constraints are sensitive to declared collations of their key columns. See Collation Sensitivity of Constraints for more details.

Restrictions on Primary Key Constraints

Primary constraints are subject to the following restrictions:

A table or view can have only one primary key.

None of the columns in the primary key can be LOB, LONG , LONG RAW , VARRAY , NESTED TABLE , BFILE , REF , TIMESTAMP WITH TIME ZONE , or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE .

The size of the primary key cannot exceed approximately one database block.

A composite primary key cannot have more than 32 columns.

You cannot designate the same column or combination of columns as both a primary key and a unique key.

You cannot specify a primary key when creating a subview in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.

When you specify a primary key constraint for an external table, you must specify the RELY and DISABLE constraint states. See External Table Constraints for more information.

Foreign Key Constraints

A foreign key constraint (also called a referential integrity constraint ) designates a column as the foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key . A composite foreign key designates a combination of columns as the foreign key.

The table or view containing the foreign key is called the child object, and the table or view containing the referenced key is called the parent object. The foreign key and the referenced key can be in the same table or view. In this case, the parent and child tables are the same. If you identify only the parent table or view and omit the column name, then the foreign key automatically references the primary key of the parent table or view. The corresponding column or columns of the foreign key and the referenced key must match in order, data types, and declared collations.

Foreign key constraints are sensitive to declared collations of the referenced primary or unique key columns. See Collation Sensitivity of Constraints for more details.

You can define a foreign key constraint on a single key column either inline or out of line. You must specify a composite foreign key and a foreign key on an attribute out of line.

To satisfy a composite foreign key constraint, the composite foreign key must refer to a composite unique key or a composite primary key in the parent table or view, or the value of at least one of the columns of the foreign key must be null.

You can designate the same column or combination of columns as both a foreign key and a primary or unique key. You can also designate the same column or combination of columns as both a foreign key and a cluster key.

You can define multiple foreign keys in a table or view. Also, a single column can be part of more than one foreign key.

Restrictions on Foreign Key Constraints

Foreign key constraints are subject to the following restrictions:

None of the columns in the foreign key can be of LOB, LONG , LONG RAW , VARRAY , NESTED TABLE , BFILE , REF , TIMESTAMP WITH TIME ZONE , or user-defined type. However, the primary key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE .

The referenced unique or primary key constraint on the parent table or view must already be defined.

A composite foreign key cannot have more than 32 columns.

The child and parent tables must be on the same database. To enable referential integrity constraints across nodes of a distributed database, you must use database triggers. See CREATE TRIGGER.

If either the child or parent object is a view, then the constraint is subject to all restrictions on view constraints. See «View Constraints» .

You cannot define a foreign key constraint in a CREATE TABLE statement that contains an AS subquery clause. Instead, you must create the table without the constraint and then add it later with an ALTER TABLE statement.

When a table has a foreign key, and the parent of the foreign key is an index-organized table, a session that updates a row that contains the foreign key can hang when another session is updating a non-key column in the parent table.

When you specify a foreign key constraint for an external table, you must specify the RELY and DISABLE constraint states. See External Table Constraints for more information.

Oracle Database Development Guide for more information on using constraints

Foreign key constraints use the references_clause syntax. When you specify a foreign key constraint inline, you need only the references_clause . When you specify a foreign key constraint out of line, you must also specify the FOREIGN KEY keywords and one or more columns.

ON DELETE Clause

The ON DELETE clause lets you determine how Oracle Database automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, then Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table.

Specify CASCADE if you want Oracle to remove dependent foreign key values.

Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL . You cannot specify this clause for a virtual column, because the values in a virtual column cannot be updated directly. Rather, the values from which the virtual column are derived must be updated.

Restriction on ON DELETE

You cannot specify this clause for a view constraint.

A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

The syntax for inline and out-of-line specification of check constraints is the same. However, inline specification can refer only to the column (or the attributes of the column if it is an object column) currently being defined, whereas out-of-line specification can refer to multiple columns or attributes.

Oracle does not verify that conditions of check constraints are not mutually exclusive. Therefore, if you create multiple check constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions.

If the condition of a check constraint depends on NLS parameters, such as NLS_DATE_FORMAT , Oracle evaluates the condition using the database values of the parameters, not the session values. You can find the database values of the NLS parameters in the data dictionary view NLS_DATABASE_PARAMETERS . These values are associated with a database by the DDL statement CREATE DATABASE and never change afterwards.

Conditions for additional information and syntax

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

You cannot specify a check constraint for a view. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.

Conditions of check constraints cannot contain the following constructs:

Subqueries and scalar subquery expressions

Calls to the functions that are not deterministic ( CURRENT_DATE , CURRENT_TIMESTAMP , DBTIMEZONE , LOCALTIMESTAMP , SESSIONTIMEZONE , SYSDATE , SYSTIMESTAMP , UID , USER , and USERENV )

Calls to user-defined functions

Dereferencing of REF columns (for example, using the DEREF function)

Nested table columns or attributes

The pseudocolumns CURRVAL , NEXTVAL , LEVEL , or ROWNUM

Date constants that are not fully specified

You cannot specify a check constraint for an external table.

REF constraints let you describe the relationship between a column of type REF and the object it references.

REF constraints use the ref_constraint syntax. You define a REF constraint either inline or out of line. Out-of-line specification requires you to specify the REF column or attribute you are further describing.

For ref_column , specify the name of a REF column of an object or relational table.

For ref_attribute , specify an embedded REF attribute within an object column of a relational table.

Both inline and out-of-line specification let you define a scope constraint, a rowid constraint, or a referential integrity constraint on a REF column.

If the scope table or referenced table of the REF column has a primary-key-based object identifier, then the REF column is a user-defined REF column .

SCOPE REF Constraints

In a table with a REF column, each REF value in the column can conceivably reference a row in a different object table. The SCOPE clause restricts the scope of references to a single table, scope_table . The values in the REF column or attribute point to objects in scope_table , in which object instances of the same type as the REF column are stored.

Specify the SCOPE clause to restrict the scope of references in the REF column to a single table. For you to specify this clause, scope_table must be in your own schema, or you must have the READ or SELECT privilege on scope_table , or you must have the READ ANY TABLE or SELECT ANY TABLE system privilege. You can specify only one scope table for each REF column.

Restrictions on Scope Constraints

Scope constraints are subject to the following restrictions:

You cannot add a scope constraint to an existing column unless the table is empty.

You cannot specify a scope constraint for the REF elements of a VARRAY column.

You must specify this clause if you specify AS subquery and the subquery returns user-defined REF data types.

You cannot subsequently drop a scope constraint from a REF column.

You cannot specify a scope constraint for an external table.

Rowid REF Constraints

Specify WITH ROWID to store the rowid along with the REF value in ref_column or ref_attribute . Storing the rowid with the REF value can improve the performance of dereferencing operations, but will also use more space. Default storage of REF values is without rowids.

The function DEREF for an example of dereferencing

Restrictions on Rowid Constraints

Rowid constraints are subject to the following restrictions:

You cannot define a rowid constraint for the REF elements of a VARRAY column.

You cannot subsequently drop a rowid constraint from a REF column.

If the REF column or attribute is scoped, then this clause is ignored and the rowid is not stored with the REF value.

You cannot specify a rowid constraint for an external table.

Referential Integrity Constraints on REF Columns

The references_clause of the ref_constraint syntax lets you define a foreign key constraint on the REF column. This clause also implicitly restricts the scope of the REF column or attribute to the referenced table. However, whereas a foreign key constraint on a non- REF column references an actual column in the parent table, a foreign key constraint on a REF column references the implicit object identifier column of the parent table.

If you do not specify a constraint name, then Oracle generates a system name for the constraint of the form SYS_C n .

If you add a referential integrity constraint to an existing REF column that is already scoped, then the referenced table must be the same as the scope table of the REF column. If you later drop the referential integrity constraint, then the REF column will remain scoped to the referenced table.

As is the case for foreign key constraints on other types of columns, you can use the references_clause alone for inline declaration. For out-of-line declaration you must also specify the FOREIGN KEY keywords plus one or more REF columns or attributes.

Restrictions on Foreign Key Constraints on REF Columns

Foreign key constraints on REF columns have the following additional restrictions:

Oracle implicitly adds a scope constraint when you add a referential integrity constraint to an existing unscoped REF column. Therefore, all the restrictions that apply for scope constraints also apply in this case.

You cannot specify a column after the object name in the references_clause .

Collation Sensitivity of Constraints

Starting with Oracle Database 12 c Release 2 (12.2), primary key, unique, and foreign key constraints are sensitive to declared collations of their key columns. A primary or unique key character column value from a new or updated row is compared with values in existing rows using the declared collation of the key column. For example, if the declared collation of the key column is the case-insensitive collation BINARY_CI , a new or updated row may be rejected if the new key column value differs from some existing key value only by case. The collation BINARY_CI treats character values differing only by case as equal.

A foreign key character column value is compared to parent primary or unique key column values using the declared collation of the parent key column. For example, if the declared collation of the key column is the case-insensitive collation BINARY_CI , a new or updated child row may be accepted even if there is no identical parent key value for the corresponding foreign key value, provided there exists a value differing only by case.

The declared collation of a foreign key column must be the same as the collation of the corresponding parent key column.

Columns in a composite key of a constraint may have different declared collations.

When the declared collation of a key column of a constraint is a pseudo-collation, the constraint uses a corresponding variant of the collation BINARY . Pseudo-collations cannot be used directly to compare values for a constraint, because constraints are static and cannot depend on session NLS parameters on which the pseudo-collations depend. Therefore:

The pseudo-collations USING_NLS_COMP , USING_NLS_SORT , and USING_NLS_SORT_CS use the collation BINARY .

The pseudo-collation USING_NLS_COMP_CI uses the collation BINARY_CI .

The pseudo-collation USING_NLS_COMP_AI uses the collation BINARY_AI .

When the effective collation used by a primary or unique key column is not BINARY , Oracle creates a hidden virtual column for this column. The expression of the virtual column calculates collation keys for character values of the original key column. The primary key or unique constraint is internally created on the virtual column instead of the original column. The virtual column is visible in the data dictionary views of the *_TAB_COLS family. For each of these hidden virtual columns, the COLLATED_COLUMN_ID of the *_TAB_COLS views contains the internal sequence number pointing to the corresponding original key column. The hidden virtual columns count to the 1000-column limit of a table.

Specifying Constraint State

You can specify how and when Oracle should enforce the constraint when you define the constraint.

You can use constraint_state with both inline and out-of-line specification. Except for the clauses DEFERRABLE and INITIALLY , that may be specified in any order, you must specify the rest of the component clauses in the order shown, and each clause only once.

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT ( S ) statement. If you omit this clause, then the default is NOT DEFERRABLE .

Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT [ S ] clause to defer checking of this constraint until the transaction is committed. The checking of a NOT DEFERRABLE constraint can never be deferred to the end of the transaction.

If you declare a new constraint NOT DEFERRABLE , then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT [ S ] clause to defer checking of this constraint until a COMMIT statement is submitted. If the constraint check fails, then the database returns an error and the transaction is not committed. This setting in effect lets you disable the constraint temporarily while making changes to the database that might violate the constraint until all the changes are complete.

The optimizer does not consider indexes on deferrable constraints as usable.

You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it.

SET CONSTRAINT[S] for information on setting constraint checking for a transaction

Restriction on [NOT] DEFERRABLE

You cannot specify either of these parameters for a view constraint.

The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE . The INITIALLY setting can be overridden by a SET CONSTRAINT ( S ) statement in a subsequent transaction.

Specify INITIALLY IMMEDIATE to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specify INITIALLY at all, then the default is INITIALLY IMMEDIATE .

If you declare a new constraint INITIALLY IMMEDIATE , then it must be valid at the time the CREATE TABLE or ALTER TABLE statement is committed or the statement will fail.

Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE , because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLY DEFERRED .

The RELY and NORELY parameters specify whether a constraint in NOVALIDATE mode is to be taken into account for query rewrite. Specify RELY to activate a constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The constraint is in NOVALIDATE mode, so Oracle does not enforce it. The default is NORELY .

Unenforced constraints are generally useful only with materialized views and query rewrite. Depending on the QUERY_REWRITE_INTEGRITY mode, query rewrite can use only constraints that are in VALIDATE mode, or that are in NOVALIDATE mode with the RELY parameter set, to determine join information.

Restriction on the RELY Clause

You cannot set a nondeferrable NOT NULL constraint to RELY .

Oracle Database Data Warehousing Guide for more information on materialized views and query rewrite

Using Indexes to Enforce Constraints

When defining the state of a unique or primary key constraint, you can specify an index for Oracle to use to enforce the constraint, or you can instruct Oracle to create the index used to enforce the constraint.

You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

If you specify schema . index , then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle returns an error.

If you specify the create_index_statement , then Oracle attempts to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, then Oracle returns an error.

If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

The index receives the same name as the constraint.

If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

Restrictions on the using_index_clause

The following restrictions apply to the using_index_clause :

You cannot specify this clause for a view constraint.

You cannot specify this clause for a NOT NULL , foreign key, or check constraint.

You cannot specify an index ( schema.index ) or create an index ( create_index_statement ) when enabling the primary key of an index-organized table.

You cannot specify the parallel_clause of index_attributes .

The INDEXTYPE IS . clause of index_properties is not valid in the definition of a constraint.

CREATE INDEX for a description of index_attributes , the global_partitioned_index and local_partitioned_index clauses, and for a description of NOSORT and the logging_clause in relation to indexes

Specify ENABLE if you want the constraint to be applied to the data in the table.

If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.

You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.

If any row in the table violates the integrity constraint, then the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, then Oracle enables the constraint. Subsequently, if new data violates the constraint, then Oracle does not execute the statement and returns an error indicating the integrity constraint violation.

If you place a primary key constraint in ENABLE VALIDATE mode, then the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.

ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint.

If you specify neither VALIDATE nor NOVALIDATE , then the default is VALIDATE .

If you change the state of any single constraint from ENABLE NOVALIDATE to ENABLE VALIDATE , then the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.

Restriction on the ENABLE Clause

You cannot enable a foreign key that references a disabled unique or primary key.

Specify DISABLE to disable the integrity constraint. Disabled integrity constraints appear in the data dictionary along with enabled constraints. If you do not specify this clause when creating a constraint, then Oracle automatically enables the constraint.

DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, because it lets you load large amounts of data while also saving space by not having an index. This setting lets you load data from a nonpartitioned table into a partitioned table using the exchange_partition_subpart clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.

Oracle Database Data Warehousing Guide for more information on using this setting

DISABLE NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated).

You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is in DISABLE NOVALIDATE state. Further, the optimizer can use constraints in DISABLE NOVALIDATE state.

Oracle Database SQL Tuning Guide for information on when to use this setting

If you specify neither VALIDATE nor NOVALIDATE , then the default is NOVALIDATE .

If you disable a unique or primary key constraint that is using a unique index, then Oracle drops the unique index. Refer to the CREATE TABLE enable_disable_clause for additional notes and restrictions.

The behavior of VALIDATE and NOVALIDATE depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore, the VALIDATE and NOVALIDATE keywords are described in the context of «ENABLE Clause» and «DISABLE Clause» .

Note on Foreign Key Constraints in NOVALIDATE Mode

When a foreign key constraint is in NOVALIDATE mode, if existing data in the table does not comply with the constraint and the QUERY_REWRITE_INTEGRITY parameter is not set to ENFORCED , then the optimizer may use join elimination during queries on the table. In this case, a query may return table rows with noncompliant foreign key values even if the query contains a join condition that should filter out those rows.

Handling Constraint Exceptions

When defining the state of a constraint, you can specify a table into which Oracle places the rowids of all rows violating the constraint.

Use the exceptions_clause syntax to define exception handling. If you omit schema , then Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, then Oracle assumes that the table is named EXCEPTIONS . The EXCEPTIONS table or the table you specify must exist on your local database.

You can create the EXCEPTIONS table using one of these scripts:

UTLEXCPT.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)

UTLEXPT1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables.

If you create your own exceptions table, then it must follow the format prescribed by one of these two scripts.

If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), then you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.

Restrictions on the exceptions_clause

The following restrictions apply to the exceptions_clause :

You cannot specify this clause for a view constraint.

You cannot specify this clause in a CREATE TABLE statement, because no rowids exist until after the successful completion of the statement.

The DBMS_IOT package in Oracle Database PL/SQL Packages and Types Reference for information on the SQL scripts

Oracle Database Performance Tuning Guide for information on eliminating migrated and chained rows

Oracle does not enforce view constraints. However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.

Notes on View Constraints

View constraints are a subset of table constraints and are subject to the following restrictions:

You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITH CHECK OPTION clause, which is equivalent to specifying a check constraint for the view.

View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode. You must specify the keyword DISABLE when you declare the view constraint. You need not specify NOVALIDATE explicitly, as it is the default.

The RELY and NORELY parameters are optional. View constraints, because they are unenforced, are usually specified with the RELY parameter to make them more useful. The RELY or NORELY keyword must precede the DISABLE keyword.

Because view constraints are not enforced directly, you cannot specify INITIALLY DEFERRED or DEFERRABLE .

You cannot specify the using_index_clause , the exceptions_clause clause, or the ON DELETE clause of the references_clause .

You cannot define view constraints on attributes of an object column.

External Table Constraints

Starting with Oracle Database 12 c Release 2 (12.2), you can specify NOT NULL , unique, primary key, and foreign key constraints on external tables.

NOT NULL constraints on external tables are enforced and prohibit columns from containing nulls.

Unique, primary key, and foreign key constraints are supported on external tables only in RELY DISABLE mode. You must specify the keywords RELY and DISABLE when you create these constraints. These constraints are declarative and are not enforced. They can increase query performance and reduce resource consumption because more optimizer transformations can be taken into account. In order for the optimizer to utilize these RELY DISABLE constraints, the QUERY_REWRITE_INTEGRITY initialization parameter must be set to either trusted or stale_tolerated .

Unique Key Example

The following statement is a variation of the statement that created the sample table sh.promotions . It defines inline and implicitly enables a unique key on the promo_id column (other constraints are not shown):

The constraint promo_id_u identifies the promo_id column as a unique key. This constraint ensures that no two promotions in the table have the same ID. However, the constraint does allow promotions without identifiers.

Alternatively, you can define and enable this constraint out of line:

The preceding statement also contains the using_index_clause , which specifies storage characteristics for the index that Oracle creates to enable the constraint.

Composite Unique Key Example

The following statement defines and enables a composite unique key on the combination of the warehouse_id and warehouse_name columns of the oe.warehouses table:

The wh_unq constraint ensures that the same combination of warehouse_id and warehouse_name values does not appear in the table more than once.

The ADD CONSTRAINT clause also specifies other properties of the constraint:

The USING INDEX clause specifies storage characteristics for the index Oracle creates to enable the constraint.

The EXCEPTIONS INTO clause causes Oracle to write to the wrong_id table information about any rows currently in the warehouses table that violate the constraint. If the wrong_id exceptions table does not already exist, then this statement will fail.

Primary Key Example

The following statement is a variation of the statement that created the sample table hr.locations . It creates the locations_demo table and defines and enables a primary key on the location_id column (other constraints from the hr.locations table are omitted):

The loc_id_pk constraint, specified inline, identifies the location_id column as the primary key of the locations_demo table. This constraint ensures that no two locations in the table have the same location number and that no location identifier is NULL .

Alternatively, you can define and enable this constraint out of line:

NOT NULL Example

The following statement alters the locations_demo table (created in «Primary Key Example» ) to define and enable a NOT NULL constraint on the country_id column:

The constraint country_nn ensures that no location in the table has a null country_id .

Composite Primary Key Example

The following statement defines a composite primary key on the combination of the prod_id and cust_id columns of the sample table sh.sales :

This constraint identifies the combination of the prod_id and cust_id columns as the primary key of the sales table. The constraint ensures that no two rows in the table have the same combination of values for the prod_id column and cust_id columns.

The constraint clause ( PRIMARY KEY ) also specifies the following properties of the constraint:

The constraint definition does not include a constraint name, so Oracle generates a name for the constraint.

The DISABLE clause causes Oracle to define the constraint but not enable it.

Foreign Key Constraint Example

The following statement creates the dept_20 table and defines and enables a foreign key on the department_id column that references the primary key on the department_id column of the departments table:

The constraint fk_deptno ensures that all departments given for employees in the dept_20 table are present in the departments table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the department_id column in the dept_20 table in addition to the REFERENCES constraint.

Before you define and enable this constraint, you must define and enable a constraint that designates the department_id column of the departments table as a primary or unique key.

The foreign key constraint definition does not use the FOREIGN KEY clause, because the constraint is defined inline. The data type of the department_id column is not needed, because Oracle automatically assigns to this column the data type of the referenced key.

The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the primary key of the parent table, the referenced key column names are optional.

Alternatively, you can define this foreign key constraint out of line:

The foreign key definitions in both variations of this statement omit the ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department.

ON DELETE Example

This statement creates the dept_20 table, defines and enables two referential integrity constraints, and uses the ON DELETE clause:

Because of the first ON DELETE clause, if manager number 2332 is deleted from the employees table, then Oracle sets to null the value of manager_id for all employees in the dept_20 table who previously had manager 2332.

Because of the second ON DELETE clause, Oracle cascades any deletion of a department_id value in the departments table to the department_id values of its dependent rows of the dept_20 table. For example, if Department 20 is deleted from the departments table, then Oracle deletes all of the employees in Department 20 from the dept_20 table.

Composite Foreign Key Constraint Example

The following statement defines and enables a foreign key on the combination of the employee_id and hire_date columns of the dept_20 table:

The constraint fk_empid_hiredate ensures that all the employees in the dept_20 table have employee_id and hire_date combinations that exist in the employees table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the employee_id and hire_date columns of the employees table as a primary or unique key.

The EXCEPTIONS INTO clause causes Oracle to write information to the wrong_emp table about any rows in the dept_20 table that violate the constraint. If the wrong_emp exceptions table does not already exist, then this statement will fail.

Check Constraint Examples

The following statement creates a divisions table and defines a check constraint in each column of the table:

Each constraint restricts the values of the column in which it is defined:

check_divno ensures that no division numbers are less than 10 or greater than 99.

check_divname ensures that all division names are in uppercase.

check_office restricts office locations to Dallas, Boston, Paris, or Tokyo.

Because each CONSTRAINT clause contains the DISABLE clause, Oracle only defines the constraints and does not enable them.

The following statement creates the dept_20 table, defining out of line and implicitly enabling a check constraint:

This constraint uses an inequality condition to limit an employee’s total commission, the product of salary and commission_pct , to $5000:

If an employee has non-null values for both salary and commission, then the product of these values must not exceed $5000 to satisfy the constraint.

If an employee has a null salary or commission, then the result of the condition is unknown and the employee automatically satisfies the constraint.

Because the constraint clause in this example does not supply a constraint name, Oracle generates a name for the constraint.

The following statement defines and enables a primary key constraint, two foreign key constraints, a NOT NULL constraint, and two check constraints:

The constraints enable the following rules on table data:

pk_od identifies the combination of the order_id and part_no columns as the primary key of the table. To satisfy this constraint, no two rows in the table can contain the same combination of values in the order_id and the part_no columns, and no row in the table can have a null in either the order_id or the part_no column.

fk_oid identifies the order_id column as a foreign key that references the order_id column in the orders table in the sample schema oe . All new values added to the column order_detail . order_id must already appear in the column oe.orders.order_id .

fk_pno identifies the product_id column as a foreign key that references the product_id column in the product_information table owned by oe . All new values added to the column order_detail.product_id must already appear in the column oe.product_information.product_id .

nn_qty forbids nulls in the quantity column.

check_qty ensures that values in the quantity column are always greater than zero.

check_cost ensures the values in the cost column are always greater than zero.

This example also illustrates the following points about constraint clauses and column definitions:

Out-of-line constraint definition can appear before or after the column definitions. In this example, the out-of-line definition of the pk_od constraint precedes the column definitions.

A column definition can contain multiple inline constraint definitions. In this example, the definition of the quantity column contains the definitions of both the nn_qty and check_qty constraints.

A table can have multiple CHECK constraints. Multiple CHECK constraints, each with a simple condition enforcing a single business rule, are preferable to a single CHECK constraint with a complicated condition enforcing multiple business rules. When a constraint is violated, Oracle returns an error identifying the constraint. Such an error more precisely identifies the violated business rule if the identified constraint enables a single business rule.

Case-Insensitive Constraints Example

The following statements create two tables in a parent-child relationship. The parent table is a product description table and the child table is a product component description table. Unique constraints are defined to assure that product and description values are unambiguous. For illustrative purposes, the product and component ID are case-insensitive character values. (In real-world applications, primary key IDs are usually numeric or case-normalized.)

Note that if you do not specify the data type or the collation for a foreign key column, then they are inherited from the parent key column.

The following statements add a product and its components into the tables:

Note the different case of the product ID in different component rows. Because the primary key on the product ID is declared as case-insensitive, all possible letter case combinations of the same ID are considered equal.

The following statement demonstrates that it is not possible to enter another product with the same description differing only by case. It fails with the error ORA-00001: unique constraint ( schema .PRODUCT_DESCRIPTION_UNQ) violated .

Similarly, the following statement demonstrates that the primary key contraint of the product table is case-insensitive and does not allow values differing only by case. It fails with the error ORA-00001: unique constraint ( schema .PRODUCT_PK) violated .

The following statement demonstrates that it is not possible to enter another component with the same description differing only by case. It fails with the error ORA-00001: unique constraint ( schema .PRODUCT_COMPONENT_DESCR_UNQ) violated .

Attribute-Level Constraints Example

The following example guarantees that a value exists for both the first_name and last_name attributes of the name column in the students table:

REF Constraint Examples

The following example creates a duplicate of the sample schema object type cust_address_typ , and then creates a table containing a REF column with a SCOPE constraint:

The following example creates the same table but with a referential integrity constraint on the REF column that references the object identifier column of the parent table:

The following example uses the type department_typ and the table departments_obj_t , created in «Creating Object Tables: Examples» . A table with a scoped REF is then created.

The following statement creates a table with a REF column which has a referential integrity constraint defined on it:

Explicit Index Control Example

The following statement shows another way to create a unique (or primary key) constraint that gives you explicit control over the index (or indexes) Oracle uses to enforce the constraint:

This example also shows that you can create an index for one constraint and use that index to create and enable another constraint in the same statement.

DEFERRABLE Constraint Examples

The following statement creates table games with a NOT DEFERRABLE INITIALLY IMMEDIATE constraint check (by default) on the scores column:

To define a unique constraint on a column as INITIALLY DEFERRED DEFERRABLE , issue the following statement:

PL/SQL 101: Nulls in PL/SQL

Join the DZone community and get the full member experience.

The Oracle Database supports a concept of a null value, which means, essentially, that it has no value. The idea of having nulls in a relational database is controversial, but Oracle Database supports them and you need to know how they can impact your work in PL/SQL.

First, and most important, remember that:

Null is never equal to anything else, including null. And certainly 0. And null is never not equal to anything else, including null.

You can rest assured that the code represented by . will never be executed.

Note: NULL in the above code is a literal value with a non-value of null.

The same holds true for where clause predicates in a SQL statement. The following queries will never return any rows, regardless of the contents of the employees table.

Of course, you will rarely write code that includes explicit references to the NULL literal. You are more likely to see and write code like this:

If a NULL value could be passed to my_proc for the value_in parameter, that inequality will never evaluate to true. That might be fine, but you need to give consideration very deliberately to these questions: "Could a null value be passed in here? Should I allow a null value to be passed here?"

Then take the necessary steps to bulletproof your code from nulls (see last section in this blog post for details).

With that, let’s dive into the details.

NULLs in Oracle Database

Nulls work the same in SQL and PL/SQL and, as is the case with so much else in PL/SQL, you should apply the documented descriptions from SQL to PL/SQL unless otherwise noted, so here’s the doc on Nulls in SQL.

A column’s value can be null unless you defined that column with a NOT NULL constraint, as in:

When a row is inserted into and updated in this table, the following columns cannot be set to NULL :

• ID
• USER_ID
• USER_NAME

I don’t have to provide a Twitter account name.

Wait, you might be saying: Why do you have to provide a value for ID ? You didn’t specify NOT NULL .

That’s because identity columns can never be set to NULL . Identity columns are new to 12.1 and replace the use of sequences to generate unique values for primary keys.

Moving on to PL/SQL, a variable’s value can be null unless you declared that variable with a NOT NULL constraint or use the CONSTANT keyword to define your variable as a constant. That behavior is shown in the code below.

Note that you cannot specify that a parameter in a procedure or function must be NOT NULL . Instead, you must use a datatype that cannot be null or add code to your subprogram to "protect" it from null values. See the section Bulletproofing for Nulls below.

NULLs and Empty Strings

Here’s something to keep in mind:

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

In other words, the following block displays ‘Null’ twice:

But we encourage you to not write code that assumes that this will always be the case.

Hey, having said that, the Oracle Database team has a very strong history of not changing behavior in new releases that causes problems in the 1,000,000s of lines of existing SQL and PL/SQL code out there "in the wild."

Declarations: NULL’s the Default!

Whenever you declare a variable, it’s value is set to NULL — unless you assign a different value to it. So, yes, you could do the following:

But I suggest that you do not. You take the risk of other people reading your code and thinking "Gee, I guess Steven doesn’t understand PL/SQL too well."

Instead, simply declare the variable and let the PL/SQL engine take care of the default initialization to NULL , as in:

Don’t worry; Oracle is never going to change this behavior, so you don’t have to "take out insurance."

Boolean Expressions With NULL

Study this Truth Table and make sure you are comfortable with everything you see in it. You can even verify it to yourself with this LiveSQL script.

Notice that if x is null or y is null, then x AND y is always null. But x OR y evaluates to TRUE if either x or y is TRUE , even if the other value is NULL .

The most important thing to remember is that in almost every case if any part of your expression evaluates to NULL , the entire expression will evaluate to NULL .

In addition, pay close attention to how you construct conditional statements and expressions, when an expression might evaluate to NULL .

Compare the following two blocks.

In Block 1, if expr evaluates to NULL , then do_that will be executed.

In Block 2, if expr evaluates to NULL , then neither do_this nor do_that will be executed.

But what if you want to treat two NULL s as being equal when you compare two values? Marcus in the comments below suggests creating an isEqual function that handles this for you. He was inspired by an AskTOM thread from years past (2012), in which Tom suggested using DECODE (available in SQL only), since it treats two NULL s as equal:

Here’s the implementation of Marcus’s isequal function for dates:

NULL is not just a (not) value in PL/SQL. It is also a statement, albeit a »no-op" (no operation)-it only passes control to the next statement. Here are two examples.

I use the NULL statement inside an ELSE clause. I don’t need to do this, but sometimes it’s worth being explicit and letting future programmers know that you thought about the ELSE part of this IF statement and you really really don’t want to do anything if expr is not TRUE ( FALSE or NULL0 ).

In this next block, I use NULL; as the "target" for a GOTO statement. Yes, that’s right, PL/SQL has a GOTO statement — and you should rarely if ever need to use it. If/when you do, however, you need at least one executable statement after your label. If the point of the GOTO is simply to go to the end of the subprogram, then NULL; is the perfect statement with which to finish up.

Bulletproofing for Nulls

Null values can be a real pain. They can cause unexpected failures and exceptions in your algorithms. They can mess up queries, either returning rows you didn’t want or excluding rows when you want them.

So, the first thing to do is to decide very carefully where and when you want to allow nulls. If a column’s value should never be null, then define it that way. If a variable should never be set to NULL , then add the NOT NULL constraint or define it as a constant (as appropriate).

If you are writing a subprogram (procedure or function) and a parameter’s value should never be null, you cannot add NOT NULL to the parameter definition. Instead, you will need to do one of the following:

Use a datatype for the parameter that cannot be null. These can be base datatypes, such as NATURALN , or subtypes. See the examples below.

Add code to your subprogram to check or assert that the actual argument value is not null.

So yes there are some base datatypes that are inherently NOT NULL -able, such as NATURALN .

And when I use that datatype for my parameter, it stops NULL s cold in their tracks.

What if you are passing a string to your procedure and there is no base VARCHAR2 datatype that excludes NULL s? Then, you declare your own subtype and make that new datatype not-nullable. Let’s take a look:

Note that the exceptions raised above occur before the procedure is even executed, so the exception handler of the procedure cannot trap the exception (you do not see "Error!" after trying to run pkg.no_nulls ). That may be just what you want.

But if you cannot use a NOT NULL datatype for your parameter or you want to be able to trap the exception inside the subprogram, then you should write your own assertion code that runs right at the top of the subprogram. Here’s an example:

Better yet is to use a predefined and application-standard assertion package. That way, your code could look more like this:

Don’t have an assertion package? No problem! Grab mine from LiveSQL.

Avoid NULL Headaches

It really doesn’t matter if you believe ever-so-strongly that Oracle (and other RDBMS vendors) should not have allowed NULL s in its relational database. If you’d like to go down that rabbit hole, start here.

NULL s are in Oracle Database and they are here to stay.

So, you should understand nulls, the NULL value, the NULL statement, and how nulls can cause confusion and errors.

Then take steps in your code to avoid that confusion and those errors.

Do everything that you can declaratively (such as defining a column as NOT NULL ).

Bullet-proof your subprograms with assertion logic to ensure that your program is free of nulls (when that is how it should be).

Published at DZone with permission of Steven Feuerstein , DZone MVB . See the original article here.

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

Утверждение, представленное в качестве эпиграфа, взято из документации Oracle, но вся практика до прочтения документации указывала на противоположное. Проверка путём создания пары Unique Constraint -ов подтвердила это. Налицо ошибка в документации.

А что ещё (с надеждой на безошибочность описания) можно почерпнуть из документации об Ограничениях целостности в Oracle? Я постарался выписать различные терминологические и функциональные особенности Ограничений целостности как отдельных типов объектов БД Oracle без углубления в синтаксис и подробности их использования. Многое для меня оказалось новым, не буду скрывать.

Начнём с самого начала – Oracle9i Database Concepts Release 2 (9.2). В документации выделяется понятие «Целостность данных» ( Data Integrity ), которое связывается с выполнением бизнес-правил, сопряжённых с БД. Data Integrity делится на пять типов правил, часть из которых обеспечивается «Ограничениями целостности» ( Integrity Constraints ) СУБД Oracle :

1. NULL -правило – NOT NULL ограничение;

2. уникальные значения – ограничения уникального ключа;

3. значения первичного ключа – ограничения первичного ключа;

4. правила ссылочной целостности – ограничения внешнего ключа (или «ограничения ссылочной целостности» – в документации Oracle встречаются оба названия);

5. проверка комплексного ограничения – Check -ограничения.

(Здесь слева от тире представлено правило «Целостности данных», а справа – тип «Ограничений целостности», реализующий это правило)

Четвёртый тип правил «Целостности данных» является составным, и обеспечивается «Ограничениями целостности» лишь частично:

1. выставление в NULL зависимых данных при удалении справочных данных;

2. каскадное удаление зависимых данных при удалении справочных данных;

3. а также отсутствие какого либо действия над зависимыми данными при изменении или удалении справочных данных. (Здесь для меня осталась неясность в плане отличия Restrict от No Action . Может, кто из читателей поможет обнаружить различие…)

Оставшиеся существующие подтипы четвёртого пункта «Целостности данных»:

o выставление в NULL зависимых данных при изменении справочных данных;

o каскадное изменении зависимых данных при изменении справочных данных;

o выставление в значение по умолчанию зависимых данных при изменении или удалении справочных данных;

Те типы правил «Целостности данных», которые нельзя обеспечить с помощью существующих типов «Ограничений целостности», можно реализовать с помощью триггеров. Впрочем, любые типы правил «Целостности данных» можно организовать посредством триггеров, только этот путь более сложен и менее производителен.

Далее для краткости и в силу привычки буду использовать названия «Ограничений целостности» в английском варианте (соотнесение с вышеупомянутыми русскими названиями, на мой взгляд, очевидно), а вместо «Ограничения целостности» писать просто Ограничения.

Итак, UNIQUE Key Constraint . Это Ограничение требует, чтобы каждое значение в поле ключа было уникальным. Под понятием «значение» здесь подразумевается определённая величина, а NULL-значение под данное определение не подпадает, так что одно, два, да даже все поля в ключе UNIQUE Key Constraint могут быть равны NULL. В отличие от ключа PRIMARY Key Constraint , в котором NULL-значение не допускается вовсе.

При создании UNIQUE Key Constraints или PRIMARY Key Constraints неявно создаётся уникальный индекс по тем полям таблицы, на которые накладывается данное Ограничение. Однако, если некий (неважно – уникальный или неуникальный) индекс по полям ключа уже используется, то будет использоваться именно он вместо неявного создания нового. При удалении этих Ограничений будут удаляться и индексы. Уникальные Ограничения, созданные с атрибутом DEFERRABLE (см. ниже) всегда используют неуникальные индексы. При удалении таких Ограничений неуникальные индексы остаются.

Referential Integrity Constraint требует существования в родительской ( справочной ) таблице UNIQUE Key Constraint или PRIMARY Key Constraint. При отсутствии Ограничения NOT NULL на каком либо поле, входящем в Referential Integrity Constraint , в этом поле

допускается NULL -значение, и такой Referential Integrity Constraint будет считаться правильным.

• Если на внешнем ключе отсутствует индекс. Тогда при удалении или изменении первичного ключа родительской таблицы, Oracle будет выставлять блокировку дочерней таблицы на уровне таблицы, освобождая эту блокировку сразу после её получения. Если внешний ключ определён как ON DELETE CASCADE , то удаление записей из родительской таблицы будет приводить к share-subexclusive блокировкам на дочерней таблице. Разделяемая блокировка всей дочерней таблицы также потребуется при изменении тех полей в родительской таблице, на которые ссылаются поля дочерней таблицы. Разделяемая блокировка позволяет только чтение данных, так что ни вставка, ни удаление, ни изменение данных в дочерней таблице не будут доступны до тех пор, пока не завершится транзакция на родительской таблице.
• Если на внешнем ключе присутствует индекс, то никаких блокировок на уровне таблицы уже не будет, и при любом удалении или изменении данных в родительской таблице, в дочерней таблице будут блокированы до завершения транзакции только отдельные соответствующие записи (эксклюзивная блокировка на уровне строк).

CHECK Integrity Constraints . Допускаются на одном или нескольких полях таблицы и требует в качестве результата выполнения определённого условия TRUE или UNKNOWN для каждой строки таблицы. Примечательно, что под UNKNOWN подразумевается… NULL! Иными словами, если везде (во всяком случае, следуя той же документации Oracle) NULL -значение не равно ничему, в том числе и самому себе, то здесь оно «работает» как TRUE . Забавно, не так ли?

• может использоваться только Булево выражение;
• нельзя использовать подзапросы, SQL-функции или последовательности (интересно, почему?);
• нельзя использовать SYSDATE , UID , USE R, USERENV , LEVEL , ROWNUM .

Количество CHECK Integrity Constraints неограниченно, но порядок их срабатывания непредсказуем. Ну, и при использовании строчных литералов или таких SQL -функций, как TO_CHAR, TO_DATE, TO_NUMBER с параметрами поддержки глобализации в качестве аргументов, Oracle использует значения этих параметров по умолчанию на уровне базы. Эти значения можно переписать в создаваемом CHECK Integrity Constraint .

Все перечисленные Ограничения, реализованные в Ora c le, допускают их нарушение на уровне оператора, то есть сначала оператор будет полностью выполнен (пускай он коснётся хоть миллиона строк), а потом начнётся проверка Ограничений. Хотя, возможна отложенная проверка Ограничений– до завершения транзакции (о чём речь далее).

Режим SET CONSTRAINTS.

Оператор SET CONSTRAINTS делает Ограничения или DEFERRED , или IMMEDIATE ( DEFERRED и IMMEDIATE относятся к атрибутам Ограничений, о чём речь далее) для части транзакции. Данный оператор можно использовать для установки режима либо для списка Ограничений, либо для всех ( ALL ) Ограничений. Действие данного оператора заканчивается вместе с завершением текущей транзакции, либо с началом действия ещё одного такого же оператора. Данный оператор недоступен в триггерах.

SET CONSTRAINTS … IMMEDIATE вначале вызывает проверку наличия отложенных ранее срабатываний Ограничений, а потом уже срабатывают Ограничения, вызванные выполняющимися операторами в текущей транзакции. Любое нарушение Ограничения при таком процессе будет просигнализировано ошибкой, а при достижении COMMIT’а будет вызван полный откат текущей транзакции. Оператор ALTER SESSION также может иметь выражение SET CONSTRAINTS , но только для всех Ограничений (нельзя их перечислить списком). Это эквивалентно выполнению оператора SET CONSTRAINTS в самом начале каждой транзакции.

Выполнение оператора SET CONSTRAINTS … IMMEDIATE перед самым завершением транзакции позволяет определить успешность предстоящего COMMIT’а и избежать лишних откатов.

Состояния Ограничений.

С помощью операторов CREATE TABL E или ALTER TABLE можно задавать состояние каждого Ограничения на уровне таблицы, используя следующие выражения:

• ENABLE гарантирует удовлетворение всех входных данных Ограничению;
• DISABLE позволяет входным данным не соответствовать Ограничению;
• VALIDATE гарантирует, что все уже имеющиеся в таблице данные соответствуют Ограничению;
• NOVALIDATE позволяет уже имеющимся в таблице данным не соответствовать Ограничению;

…и их комбинации:

• ENABLE VALIDATE аналогично ENABLE и гарантирует, что абсолютно все (и уже вставленные, и вставляемые) записи удовлетворяют Ограничению;
• ENABLE NO VALIDATE гарантирует удовлетворение Ограничению всех входных данных, однако уже имеющиеся в таблице данные могут не соответствовать Ограничению;
• DISABLE NOVALIDATE аналогично DISABLE . Не гарантируется удовлетворение Ограничению как входных данных, так и уже имеющихся в таблице;
• DISABLE VALIDATE отключает Ограничение, удаляет индекс, на котором оно строилось, и запрещает любые изменения на полях, входящих в Ограничение.

… и немного об особенностях применения:

· выражение ENABLE подразумевает ENABLE VALIDATE ;

· выражение DISABLE подразумевает DISABLE NOVALIDATE ;

· VALIDATE и NOVALIDATE ничего не подразумевают в отношении ENABLE и DISABLE (скажем так, они являются зависимой частью выражения при ENABLE и DISABLE );

· про создание и удаление индексов уже упоминалось;

· при изменении состояния из NOVALIDATE в VALIDATE выполняется проверка всех имеющихся в таблице данных, что может занять очень много времени. Наоборот, при приведении состояния Ограничения из VALIDATE в NOVALIDATE просто «забывается», что имеющиеся данные когда-то соответствовали Ограничению;

· перевод одиночного ограничения из состояния ENABLE NO VALIDATE в состояние ENABLE VALIDATE не блокирует чтения, записи или другие DDL операции, они могут быть выполнены параллельно.