Oracle Constraints
Types of Constraints
Constraint Description
NOT NULL : Specifies that a column cannot contain null values
UNIQUE : Designates a column or combination of columns as unique
PRIMARY KEY : Designates a column or combination of columns as the table’s primary key
FOREIGN KEY : Designates a column or combination of columns as the foreign key in a referential integrity constraint
CHECK : Specifies a condition that each row of the table must satisfy
Types of Constraints
By default, all columns in a table allow nulls. Null means absence of a value. A NOT NULL
constraint requires a column of a table to contain values.
A UNIQUE key constraint requires that every value in a column or set of columns (key) be
unique. No two rows of a table can have duplicate values in a specified column or set of columns.
Each table in the database can have at most one PRIMARY KEY constraint. A PRIMARY KEY
constraint ensures that both of the following are true:
• No two rows of a table have duplicate values in the specified column.
• Primary key columns do not contain nulls.
A CHECK integrity constraint on a column or a set of columns requires that a specified condition
be true or unknown for every row of the table.
Although the NOT NULL and CHECK constraints do not directly require DBA attention, the
primary key, unique, and foreign key constraints must be managed to ensure high availability and
acceptable performance levels.
Constraint States
An integrity constraint can be enabled (ENABLE) or disabled (DISABLE). If a constraint is
enabled, data is checked as it is entered or updated in the database. Data that does not confirm to
the constraint’s rule is prevented from being entered. If a constraint is disabled, then data that
does not confirm can be entered into the database. An integrity constraint can be in one of the
following states:
• DISABLE NOVALIDATE
• DISABLE VALIDATE
• ENABLE NOVALIDATE
• ENABLE VALIDATE
Disable Novalidate A constraint that is disable novalidate is not checked. Data in the table, as
well as new data that is entered or updated, may not conform to the rules defined by the
constraint.
Disable Validate If a constraint is in this state, then any modification of the constrained
columns is not allowed. In addition, the index on the constraint is dropped and the constraint is
disabled.
Enable Novalidate If a constraint is in this state, new data that violates the constraint cannot be
entered. However, the table may contain data that is invalid—that is, data that violates the
constraint. Enabling constraints in the novalidated state is most useful in data warehouse
configurations that are uploading valid OLTP data.
Enable Validate If a constraint is in this state, no row violating the constraint can be inserted
into the table. However, while the constraint is disabled such a row can be inserted. This row is
known as an exception to the constraint. If the constraint is in the enable novalidate state,
violations resulting from data entered while the constraint was disabled remain. The rows that
violate the constraint must be either updated or deleted in order for the constraint to be put in the
validated state.
When a constraint changes to enable validate from a disabled state, the table is locked and all data
in the table is checked for conformity. This may cause DML operations such as a data load to
wait, so it is advisable to move first from a disabled state to enable novalidate, and then to enable
validate.
Transitions between these states are governed by the following rules:
• ENABLE implies VALIDATE, unless NOVALIDATE is specified.
• DISABLE implies NOVALIDATE, unless VALIDATE is specified.
• VALIDATE and NOVALIDATE do not have default implications for the ENABLE and DISABLE states.
• When a unique or primary key moves from the DISABLE state to the ENABLE state and there
is no existing index, a unique index is created automatically. Similarly, when a unique or
primary key moves from ENABLE to DISABLE and it is enabled with a unique index, the
unique index is dropped.
• When any constraint is moved from the NOVALIDATE state to the VALIDATE state, all data
must be checked. However, moving from VALIDATE to NOVALIDATE simply forgets that
the data was ever checked.
• Moving a single constraint from the ENABLE NOVALIDATE state to the ENABLE
VALIDATE state does not block reads, writes, or other DDL statements.
Constraint Checking
You can defer checking constraints for validity until the end of the transaction.
Nondeferred or Immediate Constraints
Nondeferred constraints, also known as immediate constraints, are enforced at the end of every
DML statement. A constraint violation causes the statement to be rolled back. If a constraint
causes an action such as delete cascade, the action is taken as part of the statement that
caused it. A constraint that is defined as nondeferrable cannot be modified to be enforced at the
end of a transaction.
Deferred Constraints
Deferred constraints are constraints that are checked only when a transaction is commited. If any
constraint violations are detected at commit time, the entire transaction is rolled back. These
constraints are most useful when both the parent and child rows in a foreign key relationship are
entered at the same time, as in the case of an order entry system, where the order and the items in
the order are entered at the same time.
A constraint that is defined as deferrable can be specified as one of the following:
• Initially immediate specifies that by default it should function as an immediate constraint,
unless explicitly set otherwise.
• Initially deferred specifies that by default the constraint should only be enforced at the end of
the transaction.
Changing the Enforcement of Constraints
The SET CONSTRAINTS statement makes constraints either DEFERRED or IMMEDIATE for a
particular transaction. You can use this statement to set the mode for a list of constraint names or
for constraints. The SET CONSTRAINTS mode lasts for the duration of the transaction or until
another SET CONSTRAINTS statement resets the mode. The SET CONSTRAINTS statement is
disallowed inside triggers.
The ALTER SESSION statement also has clauses to SET CONSTRAINTS to IMMEDIATE or
DEFERRED. These clauses imply setting ALL deferrable constraints (list of constraint names
cannot be specified). The ALTER SESSION SET CONSTRAINTS statement applies to a
current session only.
ALTER SESSION
SET CONSTRAINT[S] =
{IMMEDIATE|DEFERRED|DEFAULT}
SET CONSTRAINT | CONSTRAINTS
{constraint |ALL }
{IMMEDIATE|DEFERRED}
Example by Thomas Kyte from Asktom:
http://asktom.oracle.com/pls/asktom/f?p=100:11:231672124229748::::P11_QUESTION_ID:950829834856
Here is an example. I create a table, I disable the constraint and simulate a bulk load.
I loaded some data that will violate the constraint. I “enforce” the constraint (happens
really fast) but do not validate it. This command works and in fact no new bad data may
be entered — however when we goto validate it — the bad data is found and we cannot
validate the constraint. This is typically used when the data you are bulk loading is
known to be clean — you would disable the constraints, load, and enforce the constraints
(to speed the load). After the load, you enforce (but not validate necessarily) the
constraint.
ops$tkyte@DEV816> create table t
2 ( x int,
3 constraint x_gt_zero check ( x > 0 )
4 )
5 /
create table t
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Elapsed: 00:00:00.00
ops$tkyte@DEV816>
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = ‘X_GT_ZERO’;
STATUS VALIDATED
——– ————-
ENABLED NOT VALIDATED
Elapsed: 00:00:00.02
ops$tkyte@DEV816>
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero disable;
Table altered.
Elapsed: 00:00:00.10
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = ‘X_GT_ZERO’;
STATUS VALIDATED
——– ————-
DISABLED NOT VALIDATED
Elapsed: 00:00:00.01
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t select object_id from all_objects;
23095 rows created.
Elapsed: 00:00:06.08
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t values ( -1 );
1 row created.
Elapsed: 00:00:00.01
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero enforce;
Table altered.
Elapsed: 00:00:00.08
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = ‘X_GT_ZERO’;
STATUS VALIDATED
——– ————-
ENABLED NOT VALIDATED
Elapsed: 00:00:00.01
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t values ( -2 );
insert into t values ( -2 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_GT_ZERO) violated
Elapsed: 00:00:00.02
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero validate;
alter table t modify constraint x_gt_zero validate
*
ERROR at line 1:
ORA-02293: cannot validate (OPS$TKYTE.X_GT_ZERO) – check constraint violated
Elapsed: 00:00:00.06
ops$tkyte@DEV816>
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = ‘X_GT_ZERO’;
STATUS VALIDATED
——– ————-
ENABLED NOT VALIDATED
Elapsed: 00:00:00.01
ops$tkyte@DEV816>
