Manage Learn to apply best practices and optimize your operations.

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.

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 [email protected] 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.

Dig Deeper on Oracle database design and architecture