MacLochlainns Weblog

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

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.