Referential Integrity Tips

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.

Use Referential Integrity to enforce business rules in Oracle

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. See Check Constraint Tips.
   
• 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. See NOT NULL Constraint Tips.
   
• 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.  See Primary Key Constraint Tips.
   
• 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.   See Foreign Key Constraint Tips.
   
• Unique Constraint: This constraint is used to ensure that all column values within a table never contain a duplicate entry.  See Unique Constraint Tips.

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)
  ADD
    CONSTRAINT (foreign key constraint name)
    FOREIGN KEY
    ( field name )
    REFERENCES primary_table_name
    (primary_table_primary_index_field)

Using our example listed above in the references constraint, the coding would appear as follows:

ALTER TABLE dependent
  ADD
    CONSTRAINT ( emp_num_fk )
    FOREIGN KEY
   ( 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.

Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.