Deferring referential integrity constraints

Last week, I gave a course on data warehousing. When I overviewed the theory, I noticed I had to spend some time on “disabling the the referential integrity constraints”.
I realised that I had to provide a clear explanation on what is meant by this.
The idea is rather straight forward. When we load sverela tables in a data warehouse, we might come in a situation where we load a certain table that is referenced to by another table. If we load that certain table before the referential table, we might come in a situation that we load certain fields that are not yet included in the referential table. If not dealt with properly, an error results.

Example.

Let us assume we have two tables. The DDL looks like:

CREATE TABLE MOEDER
(
  VREEMD  INTEGER
);
CREATE UNIQUE INDEX MOEDER_PK ON MOEDER
(VREEMD);
ALTER TABLE MOEDER ADD (
  CONSTRAINT MOEDER_PK
 PRIMARY KEY
 (VREEMD);

CREATE TABLE DOCHTER
(
  SLEUTEL  INTEGER,
  VREEMD   INTEGER
);
CREATE UNIQUE INDEX DOCHTER_PK ON DOCHTER
(SLEUTEL);
ALTER TABLE DOCHTER ADD (
  CONSTRAINT DOCHTER_PK
 PRIMARY KEY
 (SLEUTEL);

ALTER TABLE DOCHTER ADD (
  CONSTRAINT DOCHTER_R01 
 FOREIGN KEY (VREEMD) 
 REFERENCES MOEDER (VREEMD)
    DEFERRABLE INITIALLY DEFERRED);

We have two tables: a MOEDER table that contains referential data. We also have have DOCHTER that contains a foreign key (VREEMD). That foreign key can only contain values that are also included in table MOEDER.
Assume, we load the record (SLEUTEL, VREEMD) in DOCHTER that contains a value pair (100,2) with 2 for VREEMD. In that case 2 must be included in the MOEDER.

In a data warehouse, we might have a situation that we load DOCHTER first, followed by MOEDER.

How to deal with the situation that we also want to load data in DOCHTER that are only later loaded in MOEDER?

We solve that situation by adding the condition DEFERRABLE INITIALLY DEFERRED to the referential integrity constrant. In that case, the constraint is only checked when the commit is issued. At that moment, the MOEDER table must have values that are used in the DOCHTER table. That allows us to load the DOCHTER first, followed by a load of MOEDER and subsequently followed by a commit.

That is different from the traditional situation when the check is done at the moment of loading. When we load a DOCHTER record without a proper refence in MOEDER, we get an error. When the DEFFERABLE INITIALLY DEFERRED is added, the check is postponed to the moment of commit. That solves our problem.