Ask the Expert

Foreign keys and triggers for referential integrity explained

I just finished converting an Access 97 database to an Oracle 8 database using the Erwin ER program. When Erwin generated the DDL, it contained foreign keys and triggers for referential integrity. I'm confused about the need for both. Doesn't Oracle use the foreign key to control whether a parent can be deleted or updated? My DDL also has triggers that check this condition. Is is possible the Erwin created unnecessary triggers? Maybe if you explain the functions of the foreign key and referential triggers I'll understand better.

    Requires Free Membership to View

Referential integrity can be handled via both triggers and foreign keys. If you create a foreign key, you can specify how you want to handle its relationship to the primary key table. The default behavior of a foreign key is to RESTRICT you from deleting a row in a "parent" table if rows using the key value exist in the "child" table. You can optionally specify CASCADE for a foreign key which says that if the parent row is deleted, all records in the child table that use that value will also be deleted. There is another option to set the foreign key column value to null if the parent row is deleted.

If you want to force a delete behavior other than RESTRICT, CASCADE or SET NULL you would use a trigger. Also, Oracle doesn't have the ability to cascade updates via a foreign key, but Access does. So, likely the triggers that were created were to handle the cascading update features you had in Access.

So, what Erwin likely did when you converted was to create foreign keys that cascaded the deletes and then created triggers to handle the cascading of updates.

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: