Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

Referential Integrity Tips

Oracle Tips by Burleson Consulting

December 16, 2011


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

 
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational