Oracle: When are constraints checked?

In my understanding constraints should generally be checked at the end of a transaction.

So, in a simple example where we have one table A with the only column P, which is its primary key, and a table B with the primary key P and the foreign key F on A.P, the following should work (on empty tables):

begin

    insert into B (P, F)
    values (1, 1);
    insert into A (P)
    values (1);

    commit;
end;

However, Oracle gives me the error (in German):

[23000][2291] ORA-02291: Integritäts-Constraint (DATABASE.AB_constraint) verletzt - übergeordneter Schlüssel nicht gefunden
ORA-06512: in Zeile 3
Position: 0

which translates to Integrity-constraint violated – referenced key not found. If I reverse the order

begin

    insert into A (P)
    values (1);
    insert into B (P, F)
    values (1, 1);

    commit;
end;

it works fine. Are constraints not validated at the end of a transaction in Oracle? And if so, is there any way of enforcing this behaviour?

In the Oracle docs it’s stated that (following the ACID properties)

[t]he transaction takes the database from one consistent state to another consistent state.

So one could expect that the states in between don’t necessarily have to be consistent. This certainly applies for the example Oracle provides: Money is transfered from one account to another one, whilst in between the total sum of money doesn’t match up (isn’t consistent), as the money got removed from one account, but not yet added to the other one. So why doesn’t it seem to be the same for foreign key constraints?

Answer

And if so, is there any way of enforcing this behaviour

Yes, there it is. It’s called a deferred constraint.

If a constraint is deferred it is checked at the end of the transaction, rather when the statement is executed.

alter table some_table
   add constraint fk_something 
   foreign key (some_column) references other_table(pk_column)
   deferrable initially deferred;

See this answer for an explanation on the difference between initially deferred and initially immediate

Advertisement

Answer