MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Foreign key database trigger

with 4 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 12th, 2008 at 10:15 pm

Posted in Uncategorized

4 Responses to 'Foreign key database trigger'

Subscribe to comments with RSS or TrackBack to 'Foreign key database trigger'.

  1. 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

    13 Jul 08 at 11:16 pm

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

    dombrooks

    23 Jul 08 at 7:42 am

  3. 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

    23 Jul 08 at 3:32 pm

  4. undeniably…..

    StreetM

    8 Sep 08 at 8:56 pm

Leave a Reply