Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
This chapter explains how to enforce the business rules associated with your database and prevent the entry of invalid information into tables by using integrity constraints. Topics include the following:
You can define integrity constraints to enforce business rules on data in your tables. Business rules specify conditions and relationships that must always be true, or must always be false. Because each company defines its own policies about things like salaries, employee numbers, inventory tracking, and so on, you can specify a different set of rules for each database table.
When an integrity constraint applies to a table, all data in the table must conform to the corresponding rule. When you issue a SQL statement that modifies data in the table, Oracle Database ensures that the new data satisfies the integrity constraint, without the need to do any checking within your program.
You can enforce rules by defining integrity constraints more reliably than by adding logic to your application. Oracle Database can check that all the data in a table obeys an integrity constraint faster than an application can.
To ensure that each employee works for a valid department, first create a rule that all values in the department table are unique:
ALTER TABLE Dept_tab ADD PRIMARY KEY (Deptno);
Then, create a rule that every department listed in the employee table must match one of the values in the department table:
ALTER TABLE Emp_tab ADD FOREIGN KEY (Deptno) REFERENCES Dept_tab(Deptno);
When you add a new employee record to the table, Oracle Database automatically checks that its department number appears in the department table.
To enforce this rule without integrity constraints, you can use a trigger to query the department table and test that each new employee's department is valid. But this method is less reliable than the integrity constraint. SELECT
in Oracle Database uses "consistent read", so the query might miss uncommitted changes from other transactions.
You might enforce business rules through application logic as well as through integrity constraints, if you can filter out bad data before attempting an insert or update. This might let you provide instant feedback to the user, and reduce the load on the database. This technique is appropriate when you can determine that data values are wrong or out of range without checking against any data already in the table.
All enabled unique and primary keys require corresponding indexes. You should create these indexes by hand, rather than letting the database create them for you. Note that:
Constraints use existing indexes where possible, rather than creating new ones.
Unique and primary keys can use non-unique as well as unique indexes. They can even use just the first few columns of non-unique indexes.
At most one unique or primary key can use each non-unique index.
The column orders in the index and the constraint do not need to match.
If you need to check whether an index is used by a constraint, for example when you want to drop the index, the object number of the index used by a unique or primary key constraint is stored in CDEF$.ENABLED
for that constraint. It is not shown in any catalog view.
You should almost always index foreign keys; the database does not do this for you automatically.
By default, all columns can contain nulls. Only define NOT
NULL
constraints for columns of a table that absolutely require values at all times.
For example, a new employee's manager or hire date might be temporarily omitted. Some employees might not have a commission. Columns like these should not have NOT
NULL
integrity constraints. However, an employee name might be required from the very beginning, and you can enforce this rule with a NOT
NULL
integrity constraint.
NOT
NULL
constraints are often combined with other types of integrity constraints to further restrict the values that can exist in specific columns of a table. Use the combination of NOT
NULL
and UNIQUE
key integrity constraints to force the input of values in the UNIQUE
key; this combination of data integrity rules eliminates the possibility that any new row' data will ever attempt to conflict with an existing row's data.
Because Oracle Database indexes do not store keys that are all null, if you want to allow index-only scans of the table or some other operation that requires indexing all rows, you must put a NOT
NULL
constraint on at least one indexed column.
A NOT
NULL
constraint is specified like this:
ALTER TABLE emp MODIFY ename NOT NULL;
Figure 6-1 shows an example of a table with NOT NULL integrity constraints.
Figure 6-1 Table with NOT NULL Integrity Constraints
Assign default values to columns that contain a typical value. For example, in the DEPT_TAB
table, if most departments are located at one site, then the default value for the LOC
column can be set to this value (such as NEW
YORK
).
Default values can help avoid errors where there is a number, such as zero, that applies to a column that has no entry. For example, a default value of zero can simplify testing, by changing a test like this:
IF sal IS NOT NULL AND sal < 50000
to the simpler form:
IF sal < 50000
Depending upon your business rules, you might use default values to represent zero or false, or leave the default values as NULL to signify an unknown value.
Defaults are also useful when you use a view to make a subset of a table's columns visible. For example, you might allow users to insert rows through a view. The base table might also have a column named INSERTER
, not included in the definition of the view, to log the user that inserts each row. To record the user name automatically, define a default value that calls the USER
function:
CREATE TABLE audit_trail
(
value1 NUMBER,
value2 VARCHAR2(32),
inserter VARCHAR2(30) DEFAULT USER
);
Default values can be defined using any literal, or almost any expression, including calls to the following:
SYSDATE
SYS_CONTEXT
USER
USERENV
UID
Default values cannot include expressions that refer to a sequence, PL/SQL function, column, LEVEL
, ROWNUM
, or PRIOR
. The datatype of a default literal or expression must match or be convertible to the column datatype.
Sometimes the default value is the result of a SQL function. For example, a call to SYS_CONTEXT
can set a different default value depending on conditions such as the user name. To be used as a default value, a SQL function must have parameters that are all literals, cannot reference any columns, and cannot call any other functions.
If you do not explicitly define a default value for a column, the default for the column is implicitly set to NULL
.
You can use the keyword DEFAULT
within an INSERT
statement instead of a literal value, and the corresponding default value is inserted.
Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key:
Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.
Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.
Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.
Choose a column that does not contain any nulls. A PRIMARY
KEY
constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.
Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys.
Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.
Choose columns for unique keys carefully. The purpose of these constraints is different from that of primary keys. Unique key constraints are appropriate for any column where duplicate values are not allowed. Primary keys identify each row of the table uniquely, and typically contain values that have no significance other than being unique. Figure 6-2 shows an example of a table with a unique key constraint.
Figure 6-2 Table with a UNIQUE Key Constraint
Some examples of good unique keys include:
An employee social security number (the primary key might be the employee number)
A truck license plate number (the primary key might be the truck number)
A customer phone number, consisting of the two columns AREA_CODE
and LOCAL_PHONE
(the primary key might be the customer number)
A department name and location (the primary key might be the department number)
The constraints discussed throughout this chapter apply to tables, not views.
Although you can declare constraints on views, such constraints do not help maintain data integrity. Instead, they are used to enable query rewrites on queries involving views, which helps performance with materialized views and other data warehousing features. Such constraints are always declared with the DISABLE
keyword, and you cannot use the VALIDATE
keyword. The constraints are never enforced, and there is no associated index.
See Also: Oracle Database Data Warehousing Guide for information on query rewrite, materialized views, and the performance reasons for declaring constraints on views |
Whenever two tables contain one or more common columns, Oracle Database can enforce the relationship between the two tables through a referential integrity constraint. Define a PRIMARY
or UNIQUE
key constraint on the column in the parent table (the one that has the complete set of column values). Define a FOREIGN KEY
constraint on the column in the child table (the one whose values must refer to existing values in the other table).
See Also: "Defining Relationships Between Parent and Child Tables" for information on defining additional integrity constraints, including the foreign key |
Figure 6-3 shows a foreign key defined on the department number. It guarantees that every value in this column must match a value in the primary key of the department table. This constraint prevents erroneous department numbers from getting into the employee table.
Foreign keys can be comprised of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.
Figure 6-3 Tables with Referential Integrity Constraints
Foreign keys allow key values that are all NULL
, even if there are no matching PRIMARY
or UNIQUE
keys.
By default (without any NOT
NULL
or CHECK
clauses), the FOREIGN
KEY
constraint enforces the "match none" rule for composite foreign keys in the ANSI/ISO standard.
To enforce the match full rule for NULL
values in composite foreign keys, which requires that all components of the key be NULL
or all be non-NULL
, define a CHECK
constraint that allows only all nulls or all non-nulls in the composite foreign key. For example, with a composite key comprised of columns A
, B
, and C
:
CHECK ((A IS NULL AND B IS NULL AND C IS NULL) OR (A IS NOT NULL AND B IS NOT NULL AND C IS NOT NULL))
In general, it is not possible to use declarative referential integrity to enforce the match partial rule for NULL
values in composite foreign keys, which requires the non-NULL
portions of the key to appear in the corresponding portions in the primary or unique key of a single row in the referenced table. You can often use triggers to handle this case, as described in Chapter 9, "Coding Triggers".
Several relationships between parent and child tables can be determined by the other types of integrity constraints defined on the foreign key in the child table.
No Constraints on the Foreign Key When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key.
This model establishes a one-to-many relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in Figure 6-3, "Tables with Referential Integrity Constraints" between the employee
and department
tables. Each department (parent key) has many employees (foreign key), and some employees might not be in a department (nulls in the foreign key).
NOT NULL Constraint on the Foreign Key When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key.
Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.
UNIQUE Constraint on the Foreign Key When a UNIQUE
constraint is defined on the foreign key, only one row in the child table can reference a given parent key value. This model allows nulls in the foreign key.
This model establishes a one-to-one relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named MEMBERNO
, referring to an employee membership number in the company insurance plan. Also, a table named INSURANCE
has a primary key named MEMBERNO
, and other columns of the table keep respective information relating to an employee insurance policy. The MEMBERNO
in the employee table should be both a foreign key and a unique key:
To enforce referential integrity rules between the EMP_TAB
and INSURANCE
tables (the FOREIGN KEY
constraint)
To guarantee that each employee has a unique membership number (the UNIQUE
key constraint)
UNIQUE and NOT NULL Constraints on the Foreign Key When both UNIQUE
and NOT
NULL
constraints are defined on the foreign key, only one row in the child table can reference a given parent key value, and because NULL
values are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.
This model establishes a one-to-one relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a NOT
NULL
constraint on the MEMBERNO
column of the employee table, in addition to guaranteeing that each employee has a unique membership number, you also ensure that no undetermined values (nulls) are allowed in the MEMBERNO
column of the employee table.
Oracle Database allows a column to be referenced by multiple FOREIGN
KEY
constraints; there is no limit on the number of dependent keys. This situation might be present if a single column is part of two different composite foreign keys.
When Oracle Database checks a constraint, it signals an error if the constraint is not satisfied. You can use the SET CONSTRAINTS
statement to defer checking the validity of constraints until the end of a transaction.
Note: You cannot issue aSET CONSTRAINTS statement inside a trigger. |
The SET CONSTRAINTS
setting lasts for the duration of the transaction, or until another SET CONSTRAINTS
statement resets the mode.
Consider the following guidelines when deferring constraint checks.
You may wish to defer constraint checks on UNIQUE
and FOREIGN
keys if the data you are working with has any of the following characteristics:
Tables are snapshots.
Some tables contain a large amount of data being manipulated by another application, which may or may not return the data in the same order.
Update cascade operations on foreign keys.
After you have identified and selected the appropriate tables, make sure their FOREIGN
, UNIQUE
and PRIMARY
key constraints are created deferrable. You can do so by issuing statements similar to the following:
CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, dname VARCHAR2 (30) ); CREATE TABLE emp ( empno NUMBER, ename VARCHAR2 (30), deptno NUMBER REFERENCES (dept), CONSTRAINT epk PRIMARY KEY (empno) DEFERRABLE, CONSTRAINT efk FOREIGN KEY (deptno) REFERENCES (dept.deptno) DEFERRABLE); INSERT INTO dept VALUES (10, 'Accounting'); INSERT INTO dept VALUES (20, 'SALES'); INSERT INTO emp VALUES (1, 'Corleone', 10); INSERT INTO emp VALUES (2, 'Costanza', 20); COMMIT; SET CONSTRAINT efk DEFERRED; UPDATE dept SET deptno = deptno + 10 WHERE deptno = 20; SELECT * from emp ORDER BY deptno; EMPNO ENAME DEPTNO ----- -------------- ------- 1 Corleone 10 2 Costanza 20 UPDATE emp SET deptno = deptno + 10 WHERE deptno = 20; SELECT * FROM emp ORDER BY deptno; EMPNO ENAME DEPTNO ----- -------------- ------- 1 Corleone 10 2 Costanza 30 COMMIT;
Within the application that manipulates the data, you must set all constraints deferred before you begin processing any data. Use the following DML statement to set all constraints deferred:
SET CONSTRAINTS ALL DEFERRED;
Note: TheSET 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. |
You can check for constraint violations before committing by issuing the SET CONSTRAINTS ALL IMMEDIATE
statement just before issuing the COMMIT
. If there are any problems with a constraint, this statement will fail and the constraint causing the error will be identified. If you commit while constraints are violated, the transaction will be rolled back and you will receive an error message.
When you create a UNIQUE
or PRIMARY
key, Oracle Database checks to see if an existing index can be used to enforce uniqueness for the constraint. If there is no such index, the database creates one.
When Oracle Database uses a unique index to enforce a constraint, and constraints associated with the unique index are dropped or disabled, the index is dropped. To preserve the statistics associated with the index (because, for example, it would take a long time to re-create it), you can specify the KEEP INDEX
clause on the DROP
command for the constraint.
While enabled foreign keys reference a PRIMARY
or UNIQUE
key, you cannot disable or drop the PRIMARY
or UNIQUE
key constraint or the index.
Note: UNIQUE and PRIMARY keys with deferrable constraints must all use non-unique indexes. |
To reuse existing indexes when creating unique and primary key constraints, you can include USING INDEX
in the constraint clause. For example:
CREATE TABLE b ( b1 INTEGER, b2 INTEGER, CONSTRAINT unique1 (b1, b2) USING INDEX (CREATE UNIQUE INDEX b_index on b(b1, b2), CONSTRAINT unique2 (b1, b2) USING INDEX b_index );
You should almost always index foreign keys. The only exception is when the matching unique or primary key is never updated or deleted.
See Also: Oracle Database Concepts for information on locking mechanisms involving indexes and keys |
The declaration of a referential integrity constraint cannot specify a foreign key that references a primary or unique key of a remote table.
However, you can maintain parent/child table relationships across nodes using triggers.
See Also: Chapter 9, "Coding Triggers" for more information about triggers that enforce referential integrity |
Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can make both the parent table and the child table inaccessible.For example, assume that the child table is in the If the network connection between the two databases fails, then some DML statements against the child table (those that insert rows or update a foreign key value) cannot proceed, because the referential integrity triggers must have access to the parent table in the |
Use CHECK
constraints when you need to enforce integrity rules based on logical expressions, such as comparisons. Never use CHECK
constraints when any of the other types of integrity constraints can provide the necessary checking.
Examples of CHECK
constraints include the following:
A CHECK
constraint on employee salaries so that no salary value is greater than 10000.
A CHECK
constraint on department locations so that only the locations "BOSTON
", "NEW
YORK
", and "DALLAS
" are allowed.
A CHECK
constraint on the salary and commissions columns to prevent the commission from being larger than the salary.
A CHECK
integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false, then the statement is rolled back. The condition of a CHECK
constraint has the following limitations:
The condition must be a boolean expression that can be evaluated using the values in the row being inserted or updated.
The condition cannot contain subqueries or sequences.
The condition cannot include the SYSDATE
, UID
, USER
, or USERENV
SQL functions.
The condition cannot contain the pseudocolumns LEVEL
, PRIOR
, or ROWNUM
.
The condition cannot contain a user-defined SQL function.
When using CHECK
constraints, remember that a CHECK
constraint is violated only if the condition evaluates to false; true and unknown values (such as comparisons with nulls) do not violate a check condition. Make sure that any CHECK
constraint that you define is specific enough to enforce the rule.
For example, consider the following CHECK
constraint:
CHECK (Sal > 0 OR Comm >= 0)
At first glance, this rule may be interpreted as "do not allow a row in the employee table unless the employee salary is greater than zero or the employee commission is greater than or equal to zero." But if a row is inserted with a null salary, that row does not violate the CHECK
constraint, regardless of whether or not the commission value is valid, because the entire check condition is evaluated as unknown. In this case, you can prevent such violations by placing NOT
NULL
integrity constraints on both the SAL
and COMM
columns.
Note: If you are not sure when unknown values result inNULL conditions, review the truth tables for the logical operators AND and OR in Oracle Database SQL Reference |
A single column can have multiple CHECK
constraints that reference the column in its definition. There is no limit to the number of CHECK
constraints that can be defined that reference a column.
The order in which the constraints are evaluated is not defined, so be careful not to rely on the order or to define multiple constraints that conflict with each other.
According to the ANSI/ISO standard, a NOT
NULL
integrity constraint is an example of a CHECK
integrity constraint, where the condition is the following:
CHECK (Column_name IS NOT NULL)
Therefore, NOT
NULL
integrity constraints for a single column can, in practice, be written in two forms: using the NOT
NULL
constraint or a CHECK
constraint. For ease of use, you should always choose to define NOT
NULL
integrity constraints, instead of CHECK
constraints with the IS
NOT
NULL
condition.
In the case where a composite key can allow only all nulls or all values, you must use a CHECK
integrity constraint. For example, the following expression of a CHECK
integrity constraint allows a key value in the composite key made up of columns C1
and C2
to contain either all nulls or all values:
CHECK ((C1 IS NULL AND C2 IS NULL) OR (C1 IS NOT NULL AND C2 IS NOT NULL))
Here are some examples showing how to create simple constraints during the prototype phase of your database design.
Each constraint is given a name in these examples. Naming the constraints prevents the database from creating multiple copies of the same constraint, with different system-generated names, if the DDL is run multiple times.
See Also: Oracle Database Administrator's Guide for information on creating and maintaining constraints for a large production database |
The following examples of CREATE
TABLE
statements show the definition of several integrity constraints:
CREATE TABLE Dept_tab ( Deptno NUMBER(3) CONSTRAINT Dept_pkey PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), CONSTRAINT Loc_check1 CHECK (loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); CREATE TABLE Emp_tab ( Empno NUMBER(5) CONSTRAINT Emp_pkey PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT dept_fkey REFERENCES Dept_tab ON DELETE CASCADE);
You can also define integrity constraints using the constraint clause of the ALTER
TABLE
command. For example:
CREATE UNIQUE INDEX I_dept ON Dept_tab(deptno); ALTER TABLE Dept_tab ADD CONSTRAINT Dept_pkey PRIMARY KEY (deptno); ALTER TABLE Emp_tab ADD CONSTRAINT Dept_fkey FOREIGN KEY (Deptno) REFERENCES Dept_tab; ALTER TABLE Emp_tab MODIFY (Ename VARCHAR2(15) NOT NULL);
You cannot create a validated constraint on a table if the table already contains any rows that would violate the constraint.
The creator of a constraint must have the ability to create tables (the CREATE
TABLE
or CREATE
ANY
TABLE
system privilege), or the ability to alter the table (the ALTER
object privilege for the table or the ALTER
ANY
TABLE
system privilege) with the constraint. Additionally, UNIQUE
and PRIMARY
KEY
integrity constraints require that the owner of the table have either a quota for the tablespace that contains the associated index or the UNLIMITED
TABLESPACE
system privilege. FOREIGN
KEY
integrity constraints also require some additional privileges.
Assign names to constraints NOT
NULL
, UNIQUE
KEY
, PRIMARY
KEY
, FOREIGN
KEY,
and CHECK
using the CONSTRAINT
option of the constraint clause. This name must be unique with respect to other constraints that you own. If you do not specify a constraint name, one is assigned automatically by Oracle Database.
Picking your own name makes error messages for constraint violations more understandable, and prevents the creation of duplicate constraints with different names if the SQL statements are run more than once.
See the previous examples of the CREATE
TABLE
and ALTER
TABLE
statements for examples of the CONSTRAINT
option of the constraint
clause. Note that the name of each constraint is included with other information about the constraint in the data dictionary.
This section explains the mechanisms and procedures for manually enabling and disabling integrity constraints.
enabled constraint. When a constraint is enabled, the corresponding rule is enforced on the data values in the associated columns. The definition of the constraint is stored in the data dictionary.
disabled constraint. When a constraint is disabled, the corresponding rule is not enforced. The definition of the constraint is still stored in the data dictionary.
An integrity constraint represents an assertion about the data in a database. This assertion is always true when the constraint is enabled. The assertion may or may not be true when the constraint is disabled, because data that violates the integrity constraint can be in the database.
During day-to-day operations, constraints should always be enabled. In certain situations, temporarily disabling the integrity constraints of a table makes sense for performance reasons. For example:
When loading large amounts of data into a table using SQL*Loader
When performing batch operations that make massive changes to a table (such as changing each employee number by adding 1000 to the existing number)
When importing or exporting one table at a time
Temporarily turning off integrity constraints can speed up these operations.
If a row of a table disobeys an integrity constraint, then this row is in violation of the constraint and is called an exception to the constraint. If any exceptions exist, then the constraint cannot be enabled. The rows that violate the constraint must be updated or deleted before the constraint can be enabled.
You can identify exceptions for a specific integrity constraint as you try to enable the constraint.
When you define an integrity constraint in a CREATE
TABLE
or ALTER
TABLE
statement, Oracle Database automatically enables the constraint by default. For code clarity, you can explicitly enable the constraint by including the ENABLE
clause in its definition.
Use this technique when creating tables that start off empty, and are populated a row at a time by individual transactions. In such cases, you want to ensure that data is consistent at all times, and the performance overhead of each DML operation is small.
The following CREATE
TABLE
and ALTER
TABLE
statements both define and enable integrity constraints:
CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY); ALTER TABLE Emp_tab ADD PRIMARY KEY (Empno);
An ALTER
TABLE
statement that tries to enable an integrity constraint will fail if any existing rows of the table violate the integrity constraint. The statement is rolled back and the constraint definition is not stored and not enabled.
See Also: "Fixing Constraint Exceptions" for more information about rows that violate integrity constraints |
The following CREATE
TABLE
and ALTER
TABLE
statements both define and disable integrity constraints:
CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY DISABLE); ALTER TABLE Emp_tab ADD PRIMARY KEY (Empno) DISABLE;
Use this technique when creating tables that will be loaded with large amounts of data before anybody else accesses them, particularly if you need to cleanse data after loading it, or need to fill empty columns with sequence numbers or parent/child relationships.
An ALTER
TABLE
statement that defines and disables an integrity constraints never fails, because its rule is not enforced.
Use the ALTER
TABLE
command to:
Enable a disabled constraint, using the ENABLE
clause.
Disable an enabled constraint, using the DISABLE
clause.
Once you have finished cleansing data and filling empty columns, you can enable constraints that were disabled during data loading.
The following statements are examples of statements that enable disabled integrity constraints:
ALTER TABLE Dept_tab ENABLE CONSTRAINT Dname_ukey; ALTER TABLE Dept_tab ENABLE PRIMARY KEY ENABLE UNIQUE (Dname) ENABLE UNIQUE (Loc);
An ALTER
TABLE
statement that attempts to enable an integrity constraint fails if any of the table rows violate the integrity constraint. The statement is rolled back and the constraint is not enabled.
See Also: "Fixing Constraint Exceptions" for more information about rows that violate integrity constraints |
If you need to perform a large load or update when a table already contains data, you can temporarily disable constraints to improve performance of the bulk operation.
The following statements are examples of statements that disable enabled integrity constraints:
ALTER TABLE Dept_tab DISABLE CONSTRAINT Dname_ukey; ALTER TABLE Dept_tab DISABLE PRIMARY KEY DISABLE UNIQUE (Dname) DISABLE UNIQUE (Loc);
The preceding examples require that you know the relevant constraint names and which columns they affect. To find this information, you can query one of the data dictionary views defined for constraints, USER_CONSTRAINTS
or USER_CONS_COLUMNS
. For more information about these views, refer to "Viewing Definitions of Integrity Constraints" and Oracle Database Reference.
When enabling or disabling UNIQUE
, PRIMARY
KEY
, and FOREIGN
KEY
integrity constraints, you should be aware of several important issues and prerequisites. UNIQUE
key and PRIMARY
KEY
constraints are usually managed by the database administrator.
When you try to create or enable a constraint, and the statement fails because integrity constraint exceptions exist, the statement is rolled back. You cannot enable the constraint until all exceptions are either updated or deleted. To determine which rows violate the integrity constraint, include the EXCEPTIONS
option in the ENABLE
clause of a CREATE
TABLE
or ALTER
TABLE
statement.
See Also: Oracle Database Administrator's Guide for more information about responding to constraint exceptions |
Starting with Oracle8i, you can alter the state of an existing constraint with the MODIFY
CONSTRAINT
clause.
MODIFY CONSTRAINT Example #1
The following commands show several alternatives for whether the CHECK
constraint is enforced, and when the constraint checking is done:
CREATE TABLE X1_tab (a1 NUMBER CONSTRAINT y CHECK (a1>3) DEFERRABLE DISABLE); ALTER TABLE X1_tab MODIFY CONSTRAINT Y_cnstrt ENABLE; ALTER TABLE X1_tab MODIFY CONSTRAINT Y_cnstrt RELY; ALTER TABLE X1_tab MODIFY CONSTRAINT Y_cnstrt INITIALLY DEFERRED; ALTER TABLE X1_tab MODIFY CONSTRAINT Y_cnstrt ENABLE NOVALIDATE;
MODIFY CONSTRAINT Example #2
The following commands show several alternatives for whether the NOT NULL
constraint is enforced, and when the checking is done:
CREATE TABLE X1_tab (A1 NUMBER CONSTRAINT Y_cnstrt NOT NULL DEFERRABLE INITIALLY DEFERRED NORELY DISABLE); ALTER TABLE X1_tab ADD CONSTRAINT One_cnstrt UNIQUE(A1) DEFERRABLE INITIALLY IMMEDIATE RELY USING INDEX PCTFREE = 30 ENABLE VALIDATE; ALTER TABLE X1_tab MODIFY UNIQUE(A1) INITIALLY DEFERRED NORELY USING INDEX PCTFREE = 40 ENABLE NOVALIDATE; ALTER TABLE X1_tab MODIFY CONSTRAINT One_cnstrt INITIALLY IMMEDIATE RELY;
Modify Constraint Example #3
The following commands show several alternatives for whether the primary key constraint is enforced, and when the checking is done:
CREATE TABLE T1_tab (A1 INT, B1 INT); ALTER TABLE T1_tab add CONSTRAINT P1_cnstrt PRIMARY KEY(a1) DISABLE; ALTER TABLE T1_tab MODIFY PRIMARY KEY INITIALLY IMMEDIATE USING INDEX PCTFREE = 30 ENABLE NOVALIDATE; ALTER TABLE T1_tab MODIFY PRIMARY KEY USING INDEX PCTFREE = 35 ENABLE; ALTER TABLE T1_tab MODIFY PRIMARY KEY ENABLE NOVALIDATE;
Because constraint names must be unique, even across multiple schemas, you can encounter problems when you want to clone a table and all its constraints, because the constraint name for the new table conflicts with the one for the original table. Or, you might create a constraint with a default system-generated name, and later realize that you want to give the constraint a name that is easy to remember, so that you can easily enable and disable it.
One of the properties you can alter for a constraint is its name. The following SQL*Plus script finds the system-generated name for a constraint and changes it:
prompt Enter table name to find its primary key: accept table_name select constraint_name from user_constraints where table_name = upper('&table_name.') and constraint_type = 'P'; prompt Enter new name for its primary key: accept new_constraint set serveroutput on declare -- USER_CONSTRAINTS.CONSTRAINT_NAME is declared as VARCHAR2(30). -- Using %TYPE here protects us if the length changes in a future release. constraint_name user_constraints.constraint_name%type; begin select constraint_name into constraint_name from user_constraints where table_name = upper('&table_name.') and constraint_type = 'P'; dbms_output.put_line('The primary key for ' || upper('&table_name.') || ' is: ' || constraint_name); execute immediate 'alter table &table_name. rename constraint ' || constraint_name || ' to &new_constraint.'; end; /
Drop an integrity constraint if the rule that it enforces is no longer true or if the constraint is no longer needed. Drop an integrity constraint using the ALTER TABLE
command and the DROP
clause. For example, the following statements drop integrity constraints:
ALTER TABLE Dept_tab DROP UNIQUE (Dname); ALTER TABLE Dept_tab DROP UNIQUE (Loc); ALTER TABLE Emp_tab DROP PRIMARY KEY, DROP CONSTRAINT Dept_fkey; DROP TABLE Emp_tab CASCADE CONSTRAINTS;
When dropping UNIQUE
, PRIMARY
KEY
, and FOREIGN
KEY
integrity constraints, you should be aware of several important issues and prerequisites. UNIQUE
and PRIMARY
KEY
constraints are usually managed by the database administrator.
General information about defining, enabling, disabling, and dropping all types of integrity constraints is given in section "Dropping Integrity Constraints". The present section supplements this information, focusing specifically on issues regarding FOREIGN
KEY
integrity constraints, which enforce relationships between columns in different tables.
You must use the same datatype for corresponding columns in the dependent and referenced tables. The column names do not need to match.
Because foreign keys reference primary and unique keys of the parent table, and PRIMARY
KEY
and UNIQUE
key constraints are enforced using indexes, composite foreign keys are limited to 32 columns.
If the column list is not included in the REFERENCES
option when defining a FOREIGN
KEY
constraint (single column or composite), then Oracle Database assumes that you intend to reference the primary key of the specified table. Alternatively, you can explicitly specify the column(s) to reference in the parent table within parentheses. Oracle Database automatically checks to verify that this column list references a primary or unique key of the parent table. If it does not, then an informative error is returned.
To create a FOREIGN
KEY
constraint, the creator of the constraint must have privileged access to the parent and child tables.
The Parent Table The creator of the referential integrity constraint must own the parent table or have REFERENCES
object privileges on the columns that constitute the parent key of the parent table.
The Child Table The creator of the referential integrity constraint must have the ability to create tables (that is, the CREATE
TABLE
or CREATE
ANY
TABLE
system privilege) or the ability to alter the child table (that is, the ALTER
object privilege for the child table or the ALTER
ANY
TABLE
system privilege).
In both cases, necessary privileges cannot be obtained through a role; they must be explicitly granted to the creator of the constraint.
These restrictions allow:
The owner of the child table to explicitly decide which constraints are enforced and which other users can create constraints
The owner of the parent table to explicitly decide if foreign keys can depend on the primary and unique keys in her tables
Oracle Database allows different types of referential integrity actions to be enforced, as specified with the definition of a FOREIGN
KEY
constraint:
Prevent Delete or Update of Parent Key The default setting prevents the deletion or update of a parent key if there is a row in the child table that references the key. For example:
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab);
Delete Child Rows When Parent Key Deleted The ON DELETE CASCADE
action allows parent key data that is referenced from the child table to be deleted, but not updated. When data in the parent key is deleted, all rows in the child table that depend on the deleted parent key values are also deleted. To specify this referential action, include the ON
DELETE
CASCADE
option in the definition of the FOREIGN
KEY
constraint. For example:
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab ON DELETE CASCADE);
Set Foreign Keys to Null When Parent Key Deleted The ON DELETE SET NULL
action allows data that references the parent key to be deleted, but not updated. When referenced data in the parent key is deleted, all rows in the child table that depend on those parent key values have their foreign keys set to null. To specify this referential action, include the ON
DELETE
SET NULL
option in the definition of the FOREIGN
KEY
constraint. For example:
CREATE TABLE Emp_tab ( FOREIGN KEY (Deptno) REFERENCES Dept_tab ON DELETE SET NULL);
The data dictionary contains the following views that relate to integrity constraints:
ALL_CONSTRAINTS
ALL_CONS_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
DBA_CONSTRAINTS
DBA_CONS_COLUMNS
You can query these views to find the names of constraints, what columns they affect, and other information to help you manage constraints.
The following CREATE
TABLE
statements define a number of integrity constraints:
CREATE TABLE Dept_tab ( Deptno NUMBER(3) PRIMARY KEY, Dname VARCHAR2(15), Loc VARCHAR2(15), CONSTRAINT Dname_ukey UNIQUE (Dname, Loc), CONSTRAINT LOC_CHECK1 CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO'))); CREATE TABLE Emp_tab ( Empno NUMBER(5) PRIMARY KEY, Ename VARCHAR2(15) NOT NULL, Job VARCHAR2(10), Mgr NUMBER(5) CONSTRAINT Mgr_fkey REFERENCES Emp_tab ON DELETE CASCADE, Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(5,2), Deptno NUMBER(3) NOT NULL CONSTRAINT Dept_fkey REFERENCES Dept_tab);
Example 1: Listing All of Your Accessible Constraints The following query lists all constraints defined on all tables accessible to the user:
SELECT Constraint_name, Constraint_type, Table_name, R_constraint_name FROM User_constraints;
Considering the example statements at the beginning of this section, a list similar to this is returned:
CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME --------------- - ---------- ----------------- SYS_C00275 P DEPT_TAB DNAME_UKEY U DEPT_TAB LOC_CHECK1 C DEPT_TAB SYS_C00278 C EMP_TAB SYS_C00279 C EMP_TAB SYS_C00280 P EMP_TAB MGR_FKEY R EMP_TAB SYS_C00280 DEPT_FKEY R EMP_TAB SYS_C00275
Notice the following:
Some constraint names are user specified (such as DNAME_UKEY
), while others are system specified (such as SYS_C00275
).
Each constraint type is denoted with a different character in the CONSTRAINT_TYPE
column. The following table summarizes the characters used for each constraint type.
Constraint Type | Character |
---|---|
PRIMARY KEY |
P |
UNIQUE KEY |
U |
FOREIGN KEY |
R |
CHECK, NOT NULL |
C |
Note: An additional constraint type is indicated by the character "V " in the CONSTRAINT_TYPE column. This constraint type corresponds to constraints created using the WITH CHECK OPTION for views. |
Example 2: Distinguishing NOT NULL Constraints from CHECK Constraints In the previous example, several constraints are listed with a constraint type of C
. To distinguish which constraints are NOT
NULL
constraints and which are CHECK
constraints in the EMP_TAB
and DEPT_TAB
tables, submit the following query:
SELECT Constraint_name, Search_condition FROM User_constraints WHERE (Table_name = 'DEPT_TAB' OR Table_name = 'EMP_TAB') AND Constraint_type = 'C';
Considering the example CREATE
TABLE
statements at the beginning of this section, a list similar to this is returned:
CONSTRAINT_NAME SEARCH_CONDITION --------------- ---------------------------------------- LOC_CHECK1 loc IN ('NEW YORK', 'BOSTON', 'CHICAGO') SYS_C00278 ENAME IS NOT NULL SYS_C00279 DEPTNO IS NOT NULL
Notice that the following are explicitly listed in the SEARCH_CONDITION
column:
NOT
NULL
constraints
The conditions for user-defined CHECK
constraints
Example 3: Listing Column Names that Constitute an Integrity Constraint The following query lists all columns that constitute the constraints defined on all tables accessible to you, the user:
SELECT Constraint_name, Table_name, Column_name FROM User_cons_columns;
Considering the example statements at the beginning of this section, a list similar to this is returned:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME --------------- ----------- --------------- DEPT_FKEY EMP_TAB DEPTNO DNAME_UKEY DEPT_TAB DNAME DNAME_UKEY DEPT_TAB LOC LOC_CHECK1 DEPT_TAB LOC MGR_FKEY EMP_TAB MGR SYS_C00275 DEPT_TAB DEPTNO SYS_C00278 EMP_TAB ENAME SYS_C00279 EMP_TAB DEPTNO SYS_C00280 EMP_TAB EMPNO