|Oracle7 Server Application Developer's Guide||
Enforcing business rules with SQL statements can be even more costly in a networked environment because the SQL statements must be transmitted over a network. In such cases, using integrity constraints eliminates the performance overhead incurred by this transmission.
To ensure that each employee in the EMP table works for a department that is listed in the DEPT table, first create a PRIMARY KEY constraint on the DEPTNO column of the DEPT table with this statement:
ALTER TABLE dept ADD PRIMARY KEY (deptno)
Then create a referential integrity constraint on the DEPTNO column of the EMP table that references the primary key of the DEPT table:
ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno)
If you subsequently add a new employee record to the table, Oracle automatically ensures that its department number appears in the department table.
To enforce this rule without integrity constraints, your application must test each new employee record to ensure that its department number belongs to an existing department. This testing involves issuing a SELECT statement to query the DEPT table.
However, in some cases, you might want to enforce business rules through your application as well as through integrity constraints. Enforcing a business rule in your application might provide faster feedback to the user than an integrity constraint. For example, if your application accepts 20 values from the user and then issues an INSERT statement containing these values, you might want your user to be notified immediately after entering a value that violates a business rule.
Since integrity constraints are enforced only when a SQL statement is issued, an integrity constraint can only notify the user of a bad value after the user has entered all 20 values and the application has issued the INSERT statement. However, you can design your application to verify the integrity of each value as it is entered and notify the user immediately in the event of a bad value.
For example, in the EMP table, it might not be detrimental if an employee's manager or hire date were temporarily omitted. Also, some employees might not have a commission. Therefore, these three columns would not be good candidates for NOT NULL integrity constraints. However, it might not be permitted to have a row that does not have an employee name. Therefore, this column is a good candidate for the use of 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's data will ever attempt to conflict with an existing row's data. For more information about such combinations, see "Relationships Between Parent and Child Tables" .
Figure 6 - 1. NOT NULL Integrity Constraints
If you do not explicitly define a default value for a column, the default for the column is implicitly set to NULL.
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 into a table through a view. The view is defined to show all columns pertinent to end-user operations; however, the base table might also have a column named INSERTER, not included in the definition of the view, which logs the user that originally inserts each row of the table. The column named INSERTER can record the name of the user that inserts a row by defining the column with the USER function:
. . ., inserter VARCHAR2(30) DEFAULT USER, . . .
For another example of assigning a default column value, refer to the section "Creating Tables" .
Figure 6 - 2. A UNIQUE Key Constraint
Note: Although UNIQUE key constraints allow the input of nulls, because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.
Do not confuse the concept of a unique key with that of a primary key. Primary keys are used to identify each row of the table uniquely. Therefore, unique keys should not have the purpose of identifying rows in the table.
Some examples of good unique keys include
Whenever two tables are related by a common column (or set of columns), define a PRIMARY or UNIQUE key constraint on the column in the parent table, and define a FOREIGN KEY constraint on the column in the child table, to maintain the relationship between the two tables. Depending on this relationship, you may want to define additional integrity constraints including the foreign key, as listed in the section "Relationships Between Parent and Child Tables", .
Figure 6 - 3 shows a foreign key defined on the DEPTNO column of the EMP table. It guarantees that every value in this column must match a value in the primary key of the DEPT table (the DEPTNO column); therefore, no erroneous department numbers can exist in the DEPTNO column of the EMP table.
Foreign keys can be comprised of multiple columns. However, a composite foreign key must reference a composite primary or unique key of the exact same structure; that is, the same number of columns and datatypes. Because composite primary and unique keys are limited to 16 columns, a composite foreign key is also limited to 16 columns.
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 between EMP and DEPT; 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. However, any number of rows in the child table can reference the same parent key value.
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, one row in the child table can reference a 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 EMP table had a column named MEMBERNO, referring to an employee's membership number in the company's 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's insurance policy. The MEMBERNO in the EMP table should be both a foreign key and a unique 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 EMP 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 EMP table.
No Index on the Foreign Key Figure 6 - 4 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or deleted in the parent table. Inserts into the parent table do not require any locks on the child table.
Notice that a share lock of the entire child table is required until the transaction containing the DELETE statement for the parent table is committed. If the foreign key specifies ON DELETE CASCADE, the DELETE statement results in a table-level share-subexclusive lock on the child table. A share lock of the entire child table is also required for an UPDATE statement on the parent table that affects any columns referenced by the child table. Share locks allow reading only; therefore, no INSERT, UPDATE, or DELETE statements can be issued on the child table until the transaction containing the UPDATE or DELETE is committed. Queries are allowed on the child table.
This situation is tolerable if updates and deletes can be avoided on the parent.
INSERT, UPDATE, and DELETE statements on the child table do not acquire any locks on the parent table; although INSERT and UPDATE statements will wait for a row-lock on the index of the parent table to clear.
Figure 6 - 4. Locking mechanisms Used When No Index Is Defined on the Foreign Key
Index on the Foreign Key Figure 6 - 5 illustrates the locking mechanisms used by Oracle when an index is defined on the foreign key, and new rows are inserted, updated or deleted in the child table.
Notice that no table locks of any kind are acquired on the parent table or any of its indexes as a result of the insert, update or delete. Therefore, any type of DML statement can be issued on the parent table, including inserts, updates, deletes, and queries.
This situation is preferable if there is any update or delete activity on the parent table while update activity is taking place on the child table. Inserts, updates, and deletes on the parent table do not require any locks on the child table; although updates and deletes will wait for row-level locks on the indexes of the child table to clear.
Figure 6 - 5. Locking mechanisms Used When Index Is Defined on the Foreign Key
If the child table specifies ON DELETE CASCADE, deletes from the parent table may result in deletes from the child table. In this case, waiting and locking rules are the same as if you deleted from the child table yourself after performing the delete from the parent table.
Note: If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table. For example, assume that the child table is in the SALES database and the parent table is in the HQ database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the HQ database.
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 EMP table unless the employee's salary is greater than zero or the employee's commission is greater than or equal to zero." However, note that if a row is inserted with a null salary and a negative commission, the row does not violate the CHECK constraint because the entire check condition is evaluated as unknown. In this particular case, you can account for 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 in NULL conditions, review the truth tables for the logical operators AND and OR in the Oracle7 Server SQL Reference manual.
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))
There are additional considerations if you are using Trusted Oracle; see the Trusted Oracle7 Server Administrator's Guide for more information.
CREATE TABLE dept ( 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 ( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5) CONSTRAINT mgr_fkey REFERENCES emp, hiredate DATE, sal NUMBER(7,2), comm NUMBER(5,2), deptno NUMBER(3) NOT NULL CONSTRAINT dept_fkey REFERENCES dept ON DELETE CASCADE);
ALTER TABLE dept ADD PRIMARY KEY (deptno); ALTER TABLE emp ADD CONSTRAINT dept_fkey FOREIGN KEY (deptno) REFERENCES dept MODIFY (ename VARCHAR2(15) NOT NULL);
|Type of Constraint||Added to Existing Columns of the Table||Added with New Columns to the Table|
|NOT NULL||Cannot be defined if any row contains a null value for this column*||Cannot be defined if the table contains any rows|
|UNIQUE||Cannot be defined if duplicate values exist in the key*||Always OK|
|PRIMARY KEY||Cannot be defined if duplicate or null values exist in the key*||Cannot be defined if the table contains any rows|
|FOREIGN KEY||Cannot be defined if the foreign key has values that do not reference a parent key value*||Always OK|
|CHECK||Cannot be defined if the volume has values that do not comply with the check condition*||Always OK|
If you attempt to define a constraint with an ALTER TABLE statement and violate one of these restrictions, the statement is rolled back and an informative error is returned explaining the violation.
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. Refer to the section "Listing Integrity Constraint Definitions" for examples of data dictionary views.
When a constraint is enabled, the rule defined by the constraint is enforced on the data values in the columns that define the constraint. The definition of the constraint is stored in the data dictionary.
When a constraint is disabled, the rule defined by the constraint is not enforced on the data values in the columns included in the constraint; however, the definition of the constraint is retained in the data dictionary.
In summary, an integrity constraint can be thought of as a statement about the data in a database. This statement is always true when the constraint is enabled; however, the statement may or may not be true when the constraint is disabled because data in violation of the integrity constraint can be in the database.
Exceptions for a specific integrity constraint can be identified while attempting to enable the constraint. This procedure is discussed in the section "Exception Reporting" .
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, . . . ); ALTER TABLE emp ADD PRIMARY KEY (empno);
An ALTER TABLE statement that defines and attempts to enable an integrity constraint may fail because rows of the table may violate the integrity constraint. In this case, the statement is rolled back and the constraint definition is not stored and not enabled. Refer to the section "Exception Reporting" for more information about rows that violate 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 constraints never fails. The definition of the constraint is always allowed because its rule is not enforced.
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey; ALTER TABLE dept ENABLE PRIMARY KEY, ENABLE UNIQUE (dname, loc);
An ALTER TABLE statement that attempts to enable an integrity constraint fails when the rows of the table violate the integrity constraint. In this case, the statement is rolled back and the constraint is not enabled. Refer to the section "Exception Reporting" for more information about rows that violate integrity constraints.
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey; ALTER TABLE dept DISABLE PRIMARY KEY, DISABLE UNIQUE (dname, loc);
Because there is a danger that some constraints might not be re-enabled after a direct path load, you should check the status of the constraint after completing the load to ensure that it was enabled properly.
You should consider enabling these constraints manually after a load (and not specify the automatic enable feature). This allows you to manually create the required indexes in parallel to save time before enabling the constraint. See the Oracle7 Server Tuning manual for more information about creating indexes in parallel.
If exceptions exist when you enable a constraint, an error is returned and the integrity constraint remains disabled. When a statement is not successfully executed because integrity constraint exceptions exist, the statement is rolled back. If exceptions exist, you cannot enable the constraint until all exceptions to the constraint 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. The EXCEPTIONS option places the ROWID, table owner, table name, and constraint name of all exception rows into a specified table. For example, the following statement attempts to enable the primary key of the DEPT table; if exceptions exist, information is inserted into a table named EXCEPTIONS:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO exceptions;
Create an appropriate exceptions report table to accept information from the EXCEPTIONS option of the ENABLE clause. Create an exception table by submitting the script UTLEXCPT.SQL. The script creates a tabled named EXCEPTIONS. You can create additional exceptions tables with different names by modifying and resubmitting the script.
If duplicate primary key values exist in the DEPT table and the name of the PRIMARY KEY constraint on DEPT is SYS_C00301, the following rows might be placed in the table EXCEPTIONS by the previous statement:
SELECT * FROM exceptions; ROWID OWNER TABLE_NAME CONSTRAINT ------------------ ------ ------------ ----------- 000003A5.000C.0001 SCOTT DEPT SYS_C00301 000003A5.000D.0001 SCOTT DEPT SYS_C00301
A 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. For example:
SELECT deptno, dname, loc FROM dept, exceptions WHERE exceptions.constraint = 'SYS_C00301' AND dept.rowid = exceptions.row_id; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 10 RESEARCH DALLAS
Rows that violate a constraint must be either updated or deleted from the table that contains the constraint. If updating exceptions, you must change the value that violates the constraint to a value consistent with the constraint or a null (if allowed). After updating or deleting a row in the master table, delete the corresponding rows for the exception in the exception report table 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.
For example, to correct the exceptions in the previous examples, the following transaction might be issued:
UPDATE dept SET deptno = 20 WHERE dname = 'RESEARCH'; DELETE FROM exceptions WHERE constraint = 'SYS_C00301'; COMMIT;
When you manage exceptions, your goal should be to eliminate all exceptions in your exception report table. After eliminating all exceptions, you must re-enable the constraint; the constraint is not automatically enabled after the exceptions are handled.
While you are correcting current exceptions for a table with the constraint disabled, other users can issue statements creating new exceptions.
ALTER TABLE dept DROP UNIQUE (dname, loc); ALTER TABLE emp DROP PRIMARY KEY, DROP CONSTRAINT dept_fkey; DROP TABLE emp CASCADE CONSTRAINTS;
When dropping UNIQUE key, PRIMARY KEY, and FOREIGN KEY integrity constraints, you should be aware of several important issues and prerequisites. For more information about dropping FOREIGN KEY constraints, see "Managing FOREIGN KEY Integrity Constraints" . UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator; see the Oracle7 Server Administrator's Guide for more information.
These restrictions allow
CREATE TABLE emp ( . . ., FOREIGN KEY (deptno) REFERENCES dept);
CREATE TABLE emp ( . . ., FOREIGN KEY (deptno) REFERENCES dept ON DELETE CASCADE);
CREATE TABLE dept ( 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 ( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(5) CONSTRAINT mgr_fkey REFERENCES emp ON DELETE CASCADE, hiredate DATE, sal NUMBER(7,2), comm NUMBER(5,2), deptno NUMBER(3) NOT NULL CONSTRAINT dept_fkey REFERENCES dept);
Example 1 Listing All of Your Accessible Constraints
The following query lists all constraints defined on all tables accessible to you, 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 the one below is returned:
CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME --------------- - ----------- ------------------ SYS_C00275 P DEPT DNAME_UKEY U DEPT LOC_CHECK1 C DEPT SYS_C00278 C EMP SYS_C00279 C EMP SYS_C00280 P EMP MGR_FKEY R EMP SYS_C00280 DEPT_FKEY R EMP SYS_C00275
Notice the following:
|CHECK, NOT NULL||C|
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 and DEPT tables, issue the following query:
SELECT constraint_name, search_condition FROM user_constraints WHERE (table_name = 'DEPT' OR table_name = 'EMP') AND constraint_type = 'C';
Considering the example CREATE TABLE statements at the beginning of this section, a list similar to the one below 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 the following:
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 the one below is returned:
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME --------------- ----------- --------------- DEPT_FKEY EMP DEPTNO DNAME_UKEY DEPT DNAME DNAME_UKEY DEPT LOC LOC_CHECK1 DEPT LOC MGR_FKEY EMP MGR SYS_C00275 DEPT DEPTNO SYS_C00278 EMP ENAME SYS_C00279 EMP DEPTNO SYS_C00280 EMP EMPNO
Copyright © 1996 Oracle Corporation.
All Rights Reserved.