Writing a Database Trigger to enforce an Optional Foreign Key

You probably know foreign key constraints let you maintain referential integrity. They are perfect for mandatory relationships. Mandatory relationships require that a row can’t exist unless a primary key exists somewhere else first. Actually, the primary key can be inserted into the same table as the foreign key for a self-referencing relationship. The somewhere else can be another column in the same row for a self referencing relationship. It can also be a column in another table for an external relationship. You can use a foreign key constraint for an optional relationship. You can write a database trigger to maintain a foreign key for an optional relationship or accept the default behavior as Dominic Brooks reminded me.

The example shows you the design and implementation of such a trigger.