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