Data Management

Use referential integrity to enforce basic business rules in Oracle

To ensure the integrity of an Oracle database, basic business rules must be enforced. While applications can enforce these rules to a degree, it is often more efficient to use referential integrity via constraints for this purpose.

It is important for a database system to have a means of enforcing basic business rules. If a transaction attempts to insert, update, or delete a table row that would violate the business rules, then the database must roll back the transaction and flag an error to the application. While it is possible to enforce rules through application code, a more foolproof method of enforcement is desirable. Since tables can be accessed via SQL*PLUS and other utilities (bypassing the application checks), database-level enforcement via constraints provides a better solution.

Oracle provides a number of integrity constraints and database triggers to facilitate the management of business rules. Let's briefly explore some of the integrity constraints and then focus on the foreign key constraint as the major topic of this article.

Referential integrity (RI) rules ensure that one-to-many and many-to-many relationships are enforced within the relational schema. In addition, valid values can also be enforced with constraints. Constraints are especially important to Web Server applications, because Web Server allows constraint checking to take place at the database server, thereby relieving the Web client of the burden of checking constraints.

Several types of constraints can be applied to Oracle tables to enforce data integrity, including:
  • Check Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, a check constraint can be added to the table definition to ensure the validity of the region column.
  • Not Null Constraint: This constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.
  • Primary Key Constraint: This constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and Oracle will create a unique index on the target primary key.
  • References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times. For example, assume a one-to-many relationship between the EMPLOYEE and DEPENDENT tables; each employee may have many dependents, yet each dependent belongs to only one employee. The references constraint tells Oracle at insert time that the value in DEPENDENT.emp_num must match the EMPLOYEE.emp_num in the employee row, thereby ensuring that a valid employee exists before the dependent row is added. At SQL delete time, the references constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.
  • Unique Constraint: This constraint is used to ensure that all column values within a table never contain a duplicate entry.

Notice the distinction between unique and primary keys. While both of these constraints create a unique index, a table may only contain one primary key constraint column—but, it may have many unique constraints on other columns.

Foreign key constraint
The referential integrity, foreign key constraint, identifies a column of a table or view as the foreign key. This foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table. The table containing the foreign key is referred to as the child, and the table containing the referenced key is the parent table. The foreign key and referenced key can be in the same table (parent and child are the same table); otherwise, the parent and child tables must be in the same database.

Here's the syntax for creating a foreign key constraint:
ALTER TABLE (table_name)
    CONSTRAINT (foreign key constraint name)
    ( field name )
    REFERENCES primary_table_name

Using our example listed above in the references constraint, the coding would appear as follows:
ALTER TABLE dependent
    CONSTRAINT ( emp_num_fk )
   ( emp_num )
    REFERENCES employee ( emp_num );

While foreign key constraints should always be used in lieu of application-only coded integrity checking, it's important to realize that using referential integrity constraints adds I/O overhead to insert and update operations. The additional I/O is attributable to the fact that the foreign key must be validated with the parent table. This overhead can be minimized by assuring that referenced columns are indexed. During bulk loads, it can be advantageous to disable integrity constraints prior to the load and re-enable the constraints after the load is completed. Once constraints are re-enabled, any errors can be noted and handled.

Referential integrity sometimes needs to be double coded: once for the database and again within the application. For example, in a multipart SQL*Form, you may not become aware of a referential integrity violation until you are many pages into the form and your form attempts to commit the transaction. In Web Server applications, you do not have the luxury of making a lot of queries against the database, and you need to be careful to keep the Oracle transactions as few as possible.

Referential integrity
A database system needs to be able to enforce business rules. Referential integrity (foreign key constraint) is one way Oracle provides for maintaining business rules. Relational systems allow control of business rules with constraints, and referential integrity rules form the backbone of relational tables.

Many applications do not use foreign key referential integrity and, instead, rely upon application code to enforce business rules. This method is not foolproof because the application tables can be accessed via other means such as SQL*PLUS.

Editor's Picks