Take this self-referencing table:

create table fk_test
(
  id          integer not null primary key,
  parent_id   integer,
  foreign key (parent_id) references fk_test (id)
);

insert into fk_test (id,parent_id) values (1, null);
insert into fk_test (id,parent_id) values (2, 1);
insert into fk_test (id,parent_id) values (3, 2);
insert into fk_test (id,parent_id) values (4, 3);

The evaluation of a foreign key constraint is important if several rows are deleted with a single statement:

delete from fk_test 
where id in (2,3,4);

The above statement will fail with a row-by-row evaluation of the foreign key. If the foreign key is evaluated at the end of the statement, the delete will succeed.

This is something different than deferred constraints.

Back to the SQL Feature Comparison