5 Maintaining Data Integrity in Database Applications

FOREIGN KEY Constraints and NULL Values

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 Oracle Database PL/SQL Language Reference.

Defining Relationships Between Parent and Child Tables

Several relationships between parent and child tables can be determined by the other types of 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 5-2 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 illustrates 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 must 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.

Rules for Multiple FOREIGN KEY Constraints

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.

Deferring Constraint Checks

When Oracle Database checks a constraint, it signals an error if the constraint is not satisfied. To defer checking constraints until the end of the current transaction, use the SET CONSTRAINTS statement.

When deferring constraint checks:

• Select appropriate data.

  You might want to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of these characteristics:

  • Tables are snapshots.

  • Some tables contain a large amount of data being manipulated by another application, which might not return the data in the same order.

• Update cascade operations on foreign keys.

• Ensure that constraints are deferrable.

  After identifying the appropriate tables, ensure that their FOREIGN, UNIQUE and PRIMARY key constraints are created DEFERRABLE.

• Within the application that manipulates the data, set all constraints deferred before you begin processing any data, as follows:

  SET CONSTRAINTS ALL DEFERRED;
  

• (Optional) Check for constraint violations immediately before committing the transaction.

  Immediately before the COMMIT statement, run the SET CONSTRAINTS ALL IMMEDIATE statement. If there are any problems with a constraint, this statement fails, and identifies the constraint that caused the error. If you commit while constraints are violated, the transaction rolls back and you get an error message.

In Example 5-4, the PRIMARY and FOREIGN keys of the table emp are created DEFERRABLE and then deferred.

DROP TABLE dept; 
CREATE TABLE dept (
  deptno NUMBER PRIMARY KEY,
  dname  VARCHAR2 (30)
);
 
DROP TABLE emp; 
CREATE TABLE emp (
  empno  NUMBER,
  ename  VARCHAR2(30),
  deptno NUMBER,
  CONSTRAINT pk_emp_empno PRIMARY KEY (empno) DEFERRABLE,
  CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE
);
 
INSERT INTO dept (deptno, dname) VALUES (10, 'Accounting');
INSERT INTO dept (deptno, dname) VALUES (20, 'SALES');
 
INSERT INTO emp (empno, ename, deptno) VALUES (1, 'Corleone', 10);
INSERT INTO emp (empno, ename, deptno) VALUES (2, 'Costanza', 20);
COMMIT;
 
SET CONSTRAINTS ALL DEFERRED;
 
UPDATE dept
SET deptno = deptno + 10
WHERE deptno = 20;
 

SELECT * from dept
ORDER BY deptno;
 

    DEPTNO DNAME
---------- ------------------------------
        10 Accounting
        30 SALES
 
2 rows selected.
 

UPDATE emp
SET deptno = deptno + 10
WHERE deptno = 20;
 

1 row updated.
 

SELECT * from emp
ORDER BY deptno;
 

     EMPNO ENAME                              DEPTNO
---------- ------------------------------ ----------
         1 Corleone                               10
         2 Costanza                               30
 
2 rows selected.

The SET CONSTRAINTS applies only to the current transaction, and its setting lasts for the duration of the transaction, or until another SET CONSTRAINTS statement resets the mode. The ALTER SESSION SET CONSTRAINTS statement applies only for the current session. The defaults specified when you create a constraint remain while the constraint exists.

Restrictions on CHECK Constraints

A CHECK 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 these 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 or ROWNUM.

• The condition cannot contain the PRIOR operator.

• The condition cannot contain a user-defined function.

Designing CHECK Constraints

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. Ensure that any CHECK constraint that you define is specific enough to enforce the rule.

For example, consider this 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 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 constraints on both the SAL and COMM columns.

Rules for Multiple CHECK Constraints

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.

Choosing Between CHECK and NOT NULL Constraints

According to the ANSI/ISO standard, a NOT NULL constraint is an example of a CHECK constraint, where the condition is:

CHECK (column_name IS NOT NULL)

Therefore, you can write NOT NULL constraints for a single column using either a NOT NULL constraint or a CHECK constraint. The NOT NULL constraint is easier to use than the CHECK constraint.

In the case where a composite key can allow only all nulls or all values, you must use a CHECK constraint. For example, this CHECK 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))

Privileges Needed to Define Constraints

If you have the CREATE TABLE or CREATE ANY TABLE system privilege, then you can define constraints on the tables that you create.

If you have the ALTER ANY TABLE system privilege, then you can define constraints on any existing table.

If you have the ALTER object privilege for a specific table, then you can define constraints on that table.

UNIQUE and PRIMARY KEY constraints require that the table owner has either the UNLIMITED TABLESPACE system privilege or a quota for the tablespace that contains the associated index.

You can define FOREIGN KEY constraints if the parent table or view is in your schema or you have the REFERENCES privilege on the columns of the referenced key in the parent table or view. For more information, see "Privileges Required to Create FOREIGN KEY Constraints".

Naming Constraints

Assign names to constraints NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK using the CONSTRAINT option of the constraint clause. This name must be unique among the constraints that you own. If you do not specify a constraint name, one is assigned automatically by Oracle Database.

Choosing 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. The name of each constraint is included with other information about the constraint in the data dictionary.

Why Disable Constraints?

During day-to-day operations, keep constraints enabled. In certain situations, temporarily disabling the 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 constraints can speed up these operations.

Creating Enabled Constraints (Default)

When you define an integrity constraint (using either CREATE TABLE or ALTER TABLE), Oracle Database enables the constraint by default. For code clarity, you can explicitly enable the constraint by including the ENABLE clause in its definition, as in Example 5-7.

/* Use CREATE TABLE statement to create enabled constraint
   (ENABLE keyword is optional): */
 
DROP TABLE t1; 
CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY ENABLE);
 
/* Create table without constraint
   and then use ALTER TABLE statement to add enabled constraint
   (ENABLE keyword is optional): */

DROP TABLE t2;
CREATE TABLE t2 (Empno NUMBER(5));
 
ALTER TABLE t2 ADD PRIMARY KEY (Empno) ENABLE;

Include the ENABLE clause when defining a constraint for a table to be populated a row at a time by individual transactions. This ensures that data is always consistent, and reduces the performance overhead of each DML statement.

An ALTER TABLE statement that tries to enable an integrity constraint fails if an existing row of the table violates the integrity constraint. The statement rolls back and the constraint definition is neither stored nor enabled.

Creating Disabled Constraints

You define and disable an integrity constraint (using either CREATE TABLE or ALTER TABLE), by including the DISABLE clause in its definition, as in Example 5-8.

/* Use CREATE TABLE statement to create disabled constraint */
 
DROP TABLE t1; 
CREATE TABLE t1 (Empno NUMBER(5) PRIMARY KEY DISABLE);
 
/* Create table without constraint
   and then use ALTER TABLE statement to add disabled constraint */
 
DROP TABLE t2; 
CREATE TABLE t2 (Empno NUMBER(5));
 
ALTER TABLE t2 ADD PRIMARY KEY (Empno) DISABLE;

Include the DISABLE clause when defining a constraint for a table to have large amounts of data inserted before anybody else accesses it, particularly if you must cleanse data after inserting it, or must fill empty columns with sequence numbers or parent/child relationships.

An ALTER TABLE statement that defines and disables a constraint never fails, because its rule is not enforced.

Enabling Existing Constraints

After you have cleansed the data and filled the empty columns, you can enable constraints that were disabled during data insertion.

To enable an existing constraint, use the ALTER TABLE statement with the ENABLE clause, as in Example 5-9.

-- Create table with disabled constraints:
 
DROP TABLE DeptTab;
CREATE TABLE DeptTab (
  Deptno  NUMBER(3) PRIMARY KEY DISABLE,
  Dname   VARCHAR2(15),
  Loc     VARCHAR2(15),
  CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) DISABLE,
  CONSTRAINT c_DeptTab_Loc
  CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) DISABLE
);
 
-- Enable constraints:
 
 ALTER TABLE DeptTab
ENABLE PRIMARY KEY
ENABLE CONSTRAINT uk_DeptTab_Dname_Loc
ENABLE CONSTRAINT c_DeptTab_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.

Disabling Existing Constraints

If you must perform a large insert or update when a table contains data, you can temporarily disable constraints to improve performance of the bulk operation.

To disable an existing constraint, use the ALTER TABLE statement with the DISABLE clause, as in Example 5-10.

-- Create table with enabled constraints:
 
DROP TABLE DeptTab; 
CREATE TABLE DeptTab (
  Deptno  NUMBER(3) PRIMARY KEY ENABLE,
  Dname   VARCHAR2(15),
  Loc     VARCHAR2(15),
  CONSTRAINT uk_DeptTab_Dname_Loc UNIQUE (Dname, Loc) ENABLE,
  CONSTRAINT c_DeptTab_Loc
  CHECK (Loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')) ENABLE
);
 
-- Disable constraints:
 
ALTER TABLE DeptTab
DISABLE PRIMARY KEY
DISABLE CONSTRAINT uk_DeptTab_Dname_Loc
DISABLE CONSTRAINT c_DeptTab_Loc;

Guidelines for Enabling and Disabling Key Constraints

When enabling or disabling UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, be aware of several important issues and prerequisites. UNIQUE key and PRIMARY KEY constraints are usually managed by the database administrator.

Fixing Constraint Exceptions

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 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.

Data Types and Names for Foreign Key Columns

You must use the same data type for corresponding columns in the dependent and referenced tables. The column names need not match.

Limit on Columns in Composite Foreign Keys

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.

Foreign Key References Primary Key by Default

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.

Privileges Required to Create FOREIGN KEY Constraints

To create a FOREIGN KEY constraint, the creator of the constraint must have privileged access to the parent and child tables.

• 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.

• 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

Choosing How Foreign Keys Enforce Referential Integrity

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);