Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Writing a Database Trigger to enforce an Optional Foreign Key

leave a comment »

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.


Written by maclochlainn

July 12, 2008 at 10:28 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: