Что такое constraint sql
Сложное ограничение целостности (constraint)
Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.
Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.
Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.
Рассмотрим на примере подробности реализации. Набор данных.
Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью ‘CLERK’ в одном городе не более 2х.
В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:
— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.
Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.
Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.
На все таблицы содержащие информацию для правила контроля вешаем триггера которыми будем вставлять в emp_chk идентификаторы модифицированных сотрудников. Некоторые комментарии к триггерам. Заказчик боевого применения функционала контроля потребовал совместимость с ORACLE-9, поэтому триггера не составные (compound).
Возможность отключения констрэйнта реализована пакетной функцией var_chk.chk_on. Использование для этой цели функции дает возможность управлять контролем не только статически (через конфигурационную таблицу) но и динамически (например для разных сеансов БД). Полный текст пакета будет приведен позже.
Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.
Очистку таблицы emp_chk в начале новой транзакции осуществляют следующие триггера. Управляет очисткой пакетная переменная var_chk.first_dml_in_commit:
Создаем materialized view.
Триггер инициализирующий var_chk.first_dml_in_commit обеспечивает очистку EMP_CHK в начале транзакции.
Собственно триггер запускающий бизнес контроль.
Некоторые комментарии к тексту CHK_EMP_MV_AS. Отладка и контроль функционирования констрэйнта можно облегчить логированием. Учтем, что в случае возникновения ошибки набор данных предъявленный для commit-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для commit-a независимо от результата обработки. Снимки наборов данных помещаются в поле emp_chk_log.XML. Лог пишется пакетной функцией var_chk.write_log в таблицу:
Все бизнес правила реализованы в отдельной функции emp_logic. Функция не является членом пакета. Это позволяет разделить в разработке и сопровождении бизнес правила констрйнта и слой системных механизмов его функционирования. Ниже текст пакета var_chk.
Функция контроля бизнес правил.
Проверим велосипед на ходу.
Реальная реализация этого решения работает на трех десятках серверов центрального офиса и филиалов с весны 2015 года.
SQL Ключевое слово CONSTRAINT
SQL ADD CONSTRAINT
Команда ADD CONSTRAINT используется для создания ограничения после того, как таблица уже создана.
Следующий SQL добавляет ограничение с именем «PK_Person», которое является ограничением первичного ключа для нескольких столбцов (ID и фамилия):
Пример
DROP CONSTRAINT
Команда DROP CONSTRAINT используется для удаления уникального, первичного ключа, внешнего ключа или ограничения проверки.
Отбросьте уникальное ограничение
Чтобы удалить уникальное ограничение, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Отбросьте ограничение первичного ключа
Чтобы удалить ограничение первичного ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Удаления ограничения внешнего ключа
Чтобы удалить ограничение внешнего ключа, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Отбросьте ограничение проверки
Чтобы удалить ограничение проверки, используйте следующий SQL:
SQL Server / Oracle / MS Access:
Упражнения
Тесты
КАК СДЕЛАТЬ
ПОДЕЛИТЬСЯ
СЕРТИФИКАТЫ
Сообщить об ошибке
Если вы хотите сообщить об ошибке или сделать предложение, не стесняйтесь, присылайте нам электронное письмо:
Ваше предложение:
Спасибо, за вашу помощь!
Ваше сообщение было отправлено в SchoolsW3.
Топ Учебники
Топ Справочники
Топ Примеры
Веб Сертификаты
SQL — Ограничения
Дата публикации: 2017-12-01
От автора: ограничения SQL — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.
Ограничения могут применяться либо на уровне столбцов, либо на уровне таблицы. Ограничения на уровне столбца применяются только к одному столбцу, тогда как ограничения уровне таблицы применяются ко всей таблице.
NOT NULL Constraint — столбец не может иметь значение NULL.
DEFAULT Constraint — задает значение по умолчанию для столбца, если оно не указано.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
UNIQUE Constraint — все значения в столбце должны быть разными.
PRIMARY Key — уникальная идентификация каждой строки/записи в таблице базы данных.
FOREIGN Key — уникально идентифицирует строку/запись в любой другой таблице базы данных.
CHECK Constraint — ограничение CHECK обеспечивает, чтобы все значения в столбце удовлетворяли определенным условиям.
INDEX — используется для быстрого создания данных базы данных.
Ограничения могут указываться при создании таблицы с помощью оператора CREATE TABLE или вы можете использовать оператор ALTER TABLE для создания ограничений уже после создания таблицы.
Удаление ограничений
Любое ограничение, которое вы определили, можно удалить с помощью команды ALTER TABLE с параметром DROP CONSTRAINT.
Например, чтобы удалить ограничение первичного ключа в таблице EMPLOYEES, вы можете использовать следующую команду.
Что такое constraint sql
При создании столбцов в T-SQL мы можем использовать ряд атрибутов, ряд которых являются ограничениями. Рассмотрим эти атрибуты.
PRIMARY KEY
С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.
Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом int, они могут представлять любой другой тип.
Установка первичного ключа на уровне таблицы:
Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:
Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.
IDENTITY
Атрибут IDENTITY позволяет сделать столбец идентификатором. Этот атрибут может назначаться для столбцов числовых типов INT, SMALLINT, BIGINT, TYNIINT, DECIMAL и NUMERIC. При добавлении новых данных в таблицу SQL Server будет инкрементировать на единицу значение этого столбца у последней записи. Как правило, в роли идентификатора выступает тот же столбец, который является первичным ключом, хотя в принципе это необязательно.
Также можно использовать полную форму атрибута:
Здесь параметр seed указывает на начальное значение, с которого будет начинаться отсчет. А параметр increment определяет, насколько будет увеличиваться следующее значение. По умолчанию атрибут использует следующие значения:
То есть отсчет начинается с 1. А последующие значения увеличиваются на единицу. Но мы можем это поведение переопределить. Например:
Также следует учитывать, что в таблице только один столбец должен иметь такой атрибут.
UNIQUE
В данном случае столбцы, которые представляют электронный адрес и телефон, будут иметь уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этих столбцов будет совпадать.
Также мы можем определить этот атрибут на уровне таблицы:
NULL и NOT NULL
DEFAULT
Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.
Здесь для столбца Age предусмотрено значение по умолчанию 18.
CHECK
Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:
Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).
Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами:
Ограничения могут носить произвольные названия, но, как правило, для применяются следующие префиксы:
В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.
И также можно задать все имена ограничений через атрибуты таблицы:
constraint
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:
View Constraints 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
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.
Description of the illustration »constraint.gif»
Description of the illustration »inline_constraint.gif»
Description of the illustration »out_of_line_constraint.gif»
Description of the illustration »inline_ref_constraint.gif»
Description of the illustration »out_of_line_ref_constraint.gif»
Description of the illustration »references_clause.gif»
Description of the illustration »constraint_state.gif»
Description of the illustration »using_index_clause.gif»
Description of the illustration »index_properties.gif»
Description of the illustration »index_attributes.gif»
Description of the illustration »exceptions_clause.gif»
Oracle Database Reference for information on the data dictionary views
NOT NULL Constraints
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.
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:
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.
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.
Restrictions on Primary Key Constraints Primary constraints are subject to the following restrictions:
A table or view can have only one primary key.
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.
Foreign Key Constraints
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 and data type.
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:
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.
Oracle Database Development Guide for more information on using constraints
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.
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.
Chapter 6, «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 user-defined functions
Dereferencing of REF columns (for example, using the DEREF function)
Nested table columns or attributes
Date constants that are not fully specified
REF constraints let you describe the relationship between a column of type REF and the object it references.
ref_constraint 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.
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.
SCOPE REF Constraints
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.
Rowid REF Constraints
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.
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 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.
Specifying Constraint State
As part of constraint definition, you can specify how and when Oracle should enforce the constraint.
constraint_state You can use the constraint_state with both inline and out-of-line specification. Specify the clauses of constraint_state in the order shown, from top to bottom, and do not specify any clause more than once.
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.
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.
Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.
VALIDATE | NOVALIDATE 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».
ENABLE Clause 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.
Restriction on the ENABLE Clause You cannot enable a foreign key that references a disabled unique or primary key.
DISABLE Clause 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 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.
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.
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.
using_index_clause 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 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 an index ( schema.index ) or create an index ( create_index_statement ) when enabling the primary key of an index-organized table.
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.
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
Data warehousing applications recognize multidimensional data in the Oracle Database by identifying referential integrity constraints in the relational schema. These constraints represent primary and foreign key relationships among tables. By querying the Oracle Database data dictionary, applications can recognize such constraints and therefore recognize the multidimensional data in the database. For schema complexity or security reasons, you might want to define views on fact and dimension tables. Oracle Database provides the ability to constrain these views. By allowing constraint definitions between views, you can propagate base table constraints to the views, thereby allowing applications to recognize multidimensional data even in the restricted environment provided by the view.
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. Refer to «RELY Clause» for more information.
You cannot define view constraints on attributes of an object column.
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:
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.
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:
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:
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.
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.
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:
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 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: