Constraints

Constraints

New Constraint Clause, Properties and States
Constraints can be modified via that ALTER TABLE...MODIFY CONSTRAINT clause.
New constraint property values RELY/NORELY. Controls whether an enabled constraint will be enforced. Used by the query rewrite function of materialized views.
RELY - Enables an existing constraint without enforcement. In order for the RELY constraint property to be valid, a constraint must be in the NOVALIDATE state.
NORELY - Enables and enforces an existing constraint. DEFAULT
Two restrictions:
1. Can only use on an existing constraint with the ALTER TABLE...MODIFY CONSTRAINT clause.
2. Can not set a NOT NULL constraint to RELY.
DISABLE VALIDATE -
Disables the constraint, drops the index AND keeps the constraint valid. This can be useful for loading data into data warehouses and range partitioned tables with a distinct range of values in the unique key, by using the exchange partition clause of the ALTER TABLE command.
When the unique key is the same as the partition key, DISABLE VALIDATE saves “overhead” and has no bad effects on the load. If the unique key is not the same as the partition key, table scans are done during the exchange; increasing processing time required and can offset the benefit of loading without an index.
DISBALE NOVALIDATE - Constraint is disabled, not maintained and not guaranteed to be true by Oracle.
ENABLE - Constraint will be applied only to new data being entered into the table.
ENABLE VALIDATE - Indicates that all old or current data in the table must comply with the constraint. Also validates that the primary key has no null values. To reduce this overhead, set each column in the primary key to not null BEFORE entering data.
ENABLE NOVALIDATE - New DML actions must comply with the constraint. Does not ensure that existing data in the table complies with the constraint.
New columns in the DBA_CONSTRAINTS view:
DEFFERED
DEFFERABLE
VALIDATED
RELY
Additional Constraint Types
DEFERRABLE - defers constraint checking until the end of each transaction. Use the SET CONSTRAINTS command.
NOT DEFERRABLE - constraint checked after each DML command. DEFAULT if DEFERRABLE is not specified.
INITIALLY IMMEDIATE - sets the DEFAULT to check the constraint at the end of every DML statement.
INITIALLY DEFERRED - sets the DEFAULT to check the constraint at the end of each transaction.
Restrictions:
1. A NOT DEFERRABLE constraint can not be set to deferred using the SET CONSTRAINT command.
2. Can not specify DEFERRABLE or NOT DEFERRABLE using the ALTER TABLE...MODIFY CONSTRAINT command.
3. Must drop and re-create a constraint to change its deferability status.