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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Foreign Key Constraint Tips

Oracle Database Tips by Donald BurlesonDecember 16, 2015

Question:  I need to understand how to add a foreign key constraint statement to make sure that all rows exists in the master table.  How do you alter a column value to make it into a foreign key constraint?

Foreign Key Constraints

A foreign key constraint is used to enforce a relationship between two tables. As an example, take the case of two tables, ITEM and PART. These tables have a relationship (an item can have none, one or many parts). Foreign key constraints help to enforce that relationship.  In the DBA world, we use diagrams called Entity Relationship Diagrams (ERD) to show visually how tables relate. Here is an ERD that shows how the ITEM and PART tables relate.

Notice we have the ITEM and PART tables diagramed in two boxes. There is a line between the two. Now, notice the two lines from the PART table to the main line between the ITEM and PART tables. We call these ?crows feet?. When you see crows feet, this means that there is a one to many relationship between the tables.

In the diagram above, there is a one-to-many relationship between the ITEM and PART tables. An item may have no parts assigned to build it (perhaps it's new and in development), or it may have one or many parts that go into building the part. Notice the circle at the top of the crow's feet. Since it is not filled in, that indicates that there may not be any parts in the part table. If the circle was darkened, this would indicate that there must be at least one part associated with each item.

So, how does this relate to foreign key constraints? Well, foreign key constraints help to enforce the types of relationships between tables we have just demonstrated. In this example we will create the ITEM and PART table. In the process of doing so, we will create a foreign key relationship between the two:

Part_desc VARCHAR2(200) NOT NULL );
Item_no   NUMBER,
Part_no   NUMBER,
Item_desc varchar2(200) NOT NULL,
CONSTRAINT fk_item_part FOREIGN KEY (part_no) REFERENCES PART (part_no),
CONSTRAINT pk_item PRIMARY  KEY (item_no, part_no) );

In this example, what we are really interested in is the creation of the ITEM table. First, note that we defined the primary key as an out of line primary key. This is because it is a composite primary key and composite primary keys have to be defined out of line.

Now, we are interested in the foreign key definition. You must define foreign key constraints as out of line constraints, as we have done in our example. Here is a snippet of the command that we used:

CONSTRAINT fk_item_part FOREIGN KEY (part_no) REFERENCES part (part_no);

Note that we start out using the constraint keyword. This tells Oracle that we are about to define a constraint. Then, we name the constraint. In this case we named the constraint FK_ITEM_PART. Constraint names have to be unique for each table and can be no more than 30 characters in length. We then use the foreign key keyword to indicate that we are defining a foreign key constraint. Next we define the column in the table being created that this foreign key belongs to.

Next we use the references keyword to indicate that we are going to define the table and column that this foreign key references. The referenced table name is next listed. In this case, it's the PART table. Finally the column in the PART table that is being referenced is listed, PART_NO.

The bottom line is that now, a part cannot be added to the ITEM table, unless it's listed in the PART table. This ensures data integrity is maintained. If you need to add foreign key constraints to a table after the fact, simply use the alter table command as seen here:

ALTER TABLE my_status ADD CONSTRAINT fk_my_status
FOREIGN KEY (part_no) REFERENCES part (part_no);

Foreign key constraint example to enforce a business rule

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



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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster