Maclochlainn's 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.