Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Create an optional foreign key database trigger

with 5 comments

The following drawing illustrates the model for this business problem. You must have a contact before an address or telephone number. You can have an address without a telephone number, or a telephone number without an address. When the telephone belongs to an address, you know that it is a mandatory relationship. The cardinality also helps because the table on the many side of a one-to-many relationship always inherits the primary key from the other table.

Address Book ERD

Address Book ERD

The model uses surrogate keys for primary and foreign keys. As you know, a database foreign key constraint checks whether the value entered is found in a primary key constrained column.

There’s no problem if you require an address before you insert a telephone. The downside of that requirement is you can’t insert a telephone without an address when you make the TELEPHONE table ADDRESS_ID (foreign key column) a not null column. Sometimes you want to enter a telephone number first to begin a customer relationship. This means you can’t constrain telephone on a preexisting address.

You make the foreign key column null allowed when you need to insert a telephone before an address. This let you enter a row with a null value for the foreign key value to the address table. This means that you should be able to insert or update a foreign key column as a null value. While you can’t insert or update a foreign key column with a value not found in the primary key column, you can insert a null value. This database trigger performs a function that duplicates a foreign key database constraint:

CREATE OR REPLACE TRIGGER telephone_t1
BEFORE INSERT OR UPDATE ON telephone
FOR EACH ROW
WHEN (new.address_id IS NOT NULL)
DECLARE
-- Local variables.
e EXCEPTION;
PRAGMA EXCEPTION_INIT(e,-20001);
valid_id NUMBER;
-- Local cursor.
CURSOR c (address_id_in NUMBER) IS
SELECT address_id
FROM address
WHERE address_id = address_id_in;
BEGIN
-- Open cursor and fetch a match.
OPEN c(:new.address_id);
FETCH c INTO valid_id;
CLOSE c;
-- Raise an exception when foreign key is invalid.
IF valid_id IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid ADDRESS_ID foreign key.');
END IF;
END;
/

There are other ways to do this, like a SELECT-INTO clause but I vote for explicit cursor handling every time.

A simpler example is:

CREATE TABLE one
( one NUMBER
, CONSTRAINT one_pk PRIMARY KEY (one));

CREATE TABLE two
( two NUMBER
, one NUMBER
, CONSTRAINT two_pk PRIMARY KEY (two)
, CONSTRAINT one_fk FOREIGN KEY (one) REFERENCES one (one));

INSERT INTO one VALUES (1);
INSERT INTO two VALUES (1,NULL);

You should note that this optional foreign key is maintained without a database trigger. If you insert a not null value into the column, it will raise a foreign key violation constraint.

Written by maclochlainn

July 12, 2008 at 10:15 pm

5 Responses

Subscribe to comments with RSS.

  1. […] example shows you the design and implementation of such a trigger. Tagged with: database trigger, […]

  2. Note that the trigger solution does not ensure that a parent entry may not be subsequently deleted. While it may improve data quality, it won’t guarantee it.
    Multi-table inserts may also raise a ‘table is mutating’ error in the trigger.

    Gary

    July 13, 2008 at 11:16 pm

  3. So what exactly are the advantages the trigger has over the foreign key constraint?

    dombrooks

    July 23, 2008 at 7:42 am

  4. None, unless you want to trigger activity other than the primary key lookup. For example, if you want to queue a message to a call center or something. Typically, then the optional foreign key trigger fires an autonomous transaction to support a workflow activity. It was also convenient to illustrate the idea of what happens in an optional foreign key constraint.

    maclochlainn

    July 23, 2008 at 3:32 pm

  5. undeniably…..

    StreetM

    September 8, 2008 at 8:56 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: