Integrity constraints are rules that restrict the values for one or
more columns in a table. Constraint clauses can appear in either
This section discusses the concepts of constraints and identifies the SQL statements used to define and manage integrity constraints. The following topics are contained in this section:
Additionally, you can specify that existing data in the table must conform to the constraint (
An integrity constraint defined on a table can be in one of the following states:
It is possible to enter data that violates a constraint while that constraint is disabled. Thus, you should always enable the constraint after completing any of the operations listed in the preceding bullet list.
You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. See "Reporting Constraint Exceptions". All rows violating constraints are noted in an
Enabling a constraint does not require validation. Enabling a constraint novalidate is much faster than enabling and validating a constraint. Also, validating a constraint that is already enabled does not require any DML locks during validation (unlike validating a previously disabled constraint). Enforcement guarantees that no violations are introduced during the validation. Hence, enabling without validating enables you to reduce the downtime typically associated with enabling a constraint.
When you enable a
While enabled foreign keys reference a
The following statement renames the
The
When you issue the
To determine which rows violate the integrity constraint, issue the
You must create an appropriate exceptions report table to accept information from the
Both of these scripts create a table named
The following statement attempts to validate the
To correct the exceptions in the previous examples, you might issue the following transaction:
CREATE TABLE
or ALTER TABLE
statements, and identify the column or columns affected by the constraint and identify the conditions of the constraint.This section discusses the concepts of constraints and identifies the SQL statements used to define and manage integrity constraints. The following topics are contained in this section:
Integrity Constraint States
You can specify that a constraint is enabled (ENABLE
) or disabled (DISABLE
).
If a constraint is enabled, data is checked as it is entered or updated
in the database, and data that does not conform to the constraint is
prevented from being entered. If a constraint is disabled, then data
that does not conform can be allowed to enter the database.Additionally, you can specify that existing data in the table must conform to the constraint (
VALIDATE
). Conversely, if you specify NOVALIDATE
, you are not ensured that existing data conforms.An integrity constraint defined on a table can be in one of the following states:
-
ENABLE
,VALIDATE
-
ENABLE
,NOVALIDATE
-
DISABLE
,VALIDATE
-
DISABLE
,NOVALIDATE
Disabling Constraints
To enforce the rules defined by integrity constraints, the constraints should always be enabled. However, consider temporarily disabling the integrity constraints of a table for the following performance reasons:-
When loading large amounts of data into a table
-
When performing batch operations that make massive changes to a table
(for example, changing every employee's number by adding 1000 to the
existing number)
-
When importing or exporting one table at a time
It is possible to enter data that violates a constraint while that constraint is disabled. Thus, you should always enable the constraint after completing any of the operations listed in the preceding bullet list.
Enabling Constraints
While a constraint is enabled, 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 novalidated 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.You can identify exceptions to a specific integrity constraint while attempting to enable the constraint. See "Reporting Constraint Exceptions". All rows violating constraints are noted in an
EXCEPTIONS
table, which you can examine.Enable Novalidate Constraint State
When a constraint is in the enable novalidate state, all subsequent statements are checked for conformity to the constraint. However, any existing data in the table is not checked. A table with enable novalidated constraints can contain invalid data, but it is not possible to add new invalid data to it. Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.Enabling a constraint does not require validation. Enabling a constraint novalidate is much faster than enabling and validating a constraint. Also, validating a constraint that is already enabled does not require any DML locks during validation (unlike validating a previously disabled constraint). Enforcement guarantees that no violations are introduced during the validation. Hence, enabling without validating enables you to reduce the downtime typically associated with enabling a constraint.
Efficient Use of Integrity Constraints: A Procedure
Using integrity constraint states in the following order can ensure the best benefits:-
Disable state.
-
Perform the operation (load, export, import).
-
Enable novalidate state.
-
Enable state.
-
No locks are held.
-
All constraints can go to enable state concurrently.
-
Constraint enabling is done in parallel.
-
Concurrent activity on table is permitted.
Setting Integrity Constraints Upon Definition
When an integrity constraint is defined in aCREATE TABLE
or ALTER TABLE
statement, it can be enabled, disabled, or validated or not validated as determined by your specification of the ENABLE
/DISABLE
clause. If the ENABLE
/DISABLE
clause is not specified in a constraint definition, the database automatically enables and validates the constraint.Disabling Constraints Upon Definition
The followingCREATE TABLE
and ALTER TABLE
statements both define and disable integrity constraints:CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY DISABLE, . . . ; ALTER TABLE emp ADD PRIMARY KEY (empno) DISABLE;An
ALTER TABLE
statement that defines and disables an
integrity constraint never fails because of rows in the table that
violate the integrity constraint. The definition of the constraint is
allowed because its rule is not enforced.Enabling Constraints Upon Definition
The followingCREATE TABLE
and ALTER TABLE
statements both define and enable integrity constraints:CREATE TABLE emp ( empno NUMBER(5) CONSTRAINT emp.pk PRIMARY KEY, . . . ; ALTER TABLE emp ADD CONSTRAINT emp.pk PRIMARY KEY (empno);An
ALTER TABLE
statement that defines and attempts to
enable an integrity constraint can fail because rows of the table
violate the integrity constraint. If this case, the statement is rolled
back and the constraint definition is not stored and not enabled.When you enable a
UNIQUE
or PRIMARY KEY
constraint an associated index is created.
Note:
An efficient procedure for enabling a constraint that can make use of parallelism is described in "Efficient Use of Integrity Constraints: A Procedure".Modifying, Renaming, or Dropping Existing Integrity Constraints
You can use theALTER TABLE
statement to enable, disable, modify, or drop a constraint. When the database is using a UNIQUE
or PRIMARY KEY
index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise.While enabled foreign keys reference a
PRIMARY
or UNIQUE
key, you cannot disable or drop the PRIMARY
or UNIQUE
key constraint or the index.Disabling Enabled Constraints
The following statements disable integrity constraints. The second statement specifies that the associated indexes are to be kept.ALTER TABLE dept DISABLE CONSTRAINT dname_ukey; ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX, DISABLE UNIQUE (dname, loc) KEEP INDEX;The following statements enable novalidate disabled integrity constraints:
ALTER TABLE dept ENABLE NOVALIDATE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY, ENABLE NOVALIDATE UNIQUE (dname, loc);The following statements enable or validate disabled integrity constraints:
ALTER TABLE dept MODIFY CONSTRAINT dname_key VALIDATE; ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;The following statements enable disabled integrity constraints:
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE PRIMARY KEY, ENABLE UNIQUE (dname, loc);To disable or drop a
UNIQUE
key or PRIMARY KEY
constraint and all dependent FOREIGN KEY
constraints in a single step, use the CASCADE
option of the DISABLE
or DROP
clauses. For example, the following statement disables a PRIMARY KEY
constraint and any FOREIGN KEY
constraints that depend on it:ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
Renaming Constraints
TheALTER TABLE...RENAME CONSTRAINT
statement enables you to rename any currently existing constraint for a
table. The new constraint name must not conflict with any existing
constraint names for a user.The following statement renames the
dname_ukey
constraint for table dept
:ALTER TABLE dept RENAME CONSTRAINT dname_ukey TO dname_unikey;When you rename a constraint, all dependencies on the base table remain valid.
The
RENAME CONSTRAINT
clause provides a means of renaming system generated constraint names.Dropping Constraints
You can drop an integrity constraint if the rule that it enforces is no longer true, or if the constraint is no longer needed. You can drop the constraint using theALTER TABLE
statement with one of the following clauses:-
DROP
PRIMARY
KEY
-
DROP
UNIQUE
-
DROP
CONSTRAINT
PRIMARY KEY
constraint:ALTER TABLE dept DROP UNIQUE (dname, loc); ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX, DROP CONSTRAINT dept_fkey;If
FOREIGN KEY
s reference a UNIQUE
or PRIMARY KEY
, you must include the CASCADE CONSTRAINTS
clause in the DROP
statement, or you cannot drop the constraint.Deferring Constraint Checks
When the database checks a constraint, it signals an error if the constraint is not satisfied. You can defer checking the validity of constraints until the end of a transaction.When you issue the
SET CONSTRAINTS
statement, the SET CONSTRAINTS
mode lasts for the duration of the transaction, or until another SET CONSTRAINTS
statement resets the mode.
Notes:
-
You cannot issue a
SET CONSTRAINT
statement inside a trigger. -
Deferrable unique and primary keys must use nonunique indexes.
Set All Constraints Deferred
Within the application being used to manipulate the data, you must set all constraints deferred before you actually begin processing any data. Use the following DML statement to set all deferrable constraints deferred:SET CONSTRAINTS ALL DEFERRED;
Note:
The SET CONSTRAINTS
statement applies only to the current
transaction. The defaults specified when you create a constraint remain
as long as the constraint exists. The ALTER SESSION SET CONSTRAINTS
statement applies for the current session only.Check the Commit (Optional)
You can check for constraint violations before committing by issuing theSET CONSTRAINTS ALL IMMEDIATE
statement just before issuing the COMMIT
.
If there are any problems with a constraint, this statement fails and
the constraint causing the error is identified. If you commit while
constraints are violated, the transaction is rolled back and you receive
an error message.Reporting Constraint Exceptions
If exceptions exist when a constraint is validated, an error is returned and the integrity constraint remains novalidated. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot validate the constraint until all exceptions to the constraint are either updated or deleted.To determine which rows violate the integrity constraint, issue the
ALTER TABLE
statement with the EXCEPTIONS
option in the ENABLE
clause. The EXCEPTIONS
option places the rowid, table owner, table name, and constraint name of all exception rows into a specified table.You must create an appropriate exceptions report table to accept information from the
EXCEPTIONS
option of the ENABLE
clause before enabling the constraint. You can create an exception table by executing the UTLEXCPT.SQL
script or the UTLEXPT1.SQL
script.
Note:
Your choice of script to execute for creating the EXCEPTIONS
table is dependent upon the compatibility level of your database and the type of table you are analyzing. See the Oracle Database SQL Language Reference for more information.EXCEPTIONS
. You can create additional exceptions tables with different names by modifying and resubmitting the script.The following statement attempts to validate the
PRIMARY KEY
of the dept
table, and if exceptions exist, information is inserted into a table named EXCEPTIONS
:ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;If duplicate primary key values exist in the
dept
table and the name of the PRIMARY KEY
constraint on dept
is sys_c00610
, then the following query will display those exceptions:SELECT * FROM EXCEPTIONS;The following exceptions are shown:
fROWID OWNER TABLE_NAME CONSTRAINT ------------------ --------- -------------- ----------- AAAAZ9AABAAABvqAAB SCOTT DEPT SYS_C00610 AAAAZ9AABAAABvqAAG SCOTT DEPT SYS_C00610A more informative query would be to join the rows in an exception report table and the master table to list the actual rows that violate a specific constraint, as shown in the following statement and results:
SELECT deptno, dname, loc FROM dept, EXCEPTIONS WHERE EXCEPTIONS.constraint = 'SYS_C00610' AND dept.rowid = EXCEPTIONS.row_id; DEPTNO DNAME LOC ---------- -------------- ----------- 10 ACCOUNTING NEW YORK 10 RESEARCH DALLASAll rows that violate a constraint must be either updated or deleted from the table containing the constraint. When updating exceptions, you must change the value violating the constraint to a value consistent with the constraint or to a null. After the row in the master table is updated or deleted, the corresponding rows for the exception in the exception report table should be deleted to avoid confusion with later exception reports. The statements that update the master table and the exception report table should be in the same transaction to ensure transaction consistency.
To correct the exceptions in the previous examples, you might issue the following transaction:
UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH'; DELETE FROM EXCEPTIONS WHERE constraint = 'SYS_C00610'; COMMIT;When managing exceptions, the goal is to eliminate all exceptions in your exception report table.
Note:
While you are correcting current exceptions for a table with the
constraint disabled, it is possible for other users to issue statements
creating new exceptions. You can avoid this by marking the constraint ENABLE NOVALIDATE
before you start eliminating exceptions.
No comments:
Post a Comment