Deferred Constraints

In the Oracle DB, you can define a constraint as deferable. You can then make the constraint check fire at commit. It turns out, according to the DB people at my employer, that DB2 by IBM cannot. We have a table where rows can reference each other.

For example:

CREATE TABLE SELF_REFERENCE_EXAMPLE(
pk unsigned long;
...
another_row_pk;
CONSTRAINT FOREIGN KEY examplefk REFERENCES SELF_REFERENCE_EXAMPLE (pk);
);

And suppose we had a table like this.

pk |... |another_row_pk
=============================================
101 ... 208
208 101

Now, if we needed to move these rows to a history table, we can't move either because the other referenced row will not be in the history table.

In oracle we could make the history table fk constraint deferred and insert both rows. The fk constraint will be checked at commit, so there is not fk violation.

DB2 won't let us do the constraint deferral trick. So we have to do something like

insert 101 into the history table without its reference to 208.
insert 208 without its reference to 101.
update both rows with their respective references.
commit.