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.
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
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
NOT NULL Constraint
- 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.
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.
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.
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 )
Using our example listed above in the references constraint,
the coding would appear as follows:
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
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
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.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.