By default foreign key constraints are evaluated when the DML statement is executed. When a constraint is deferred, it is checked only when the transaction is committed. This allows for e.g. insert/delete statements that don't need to take the processing order into account:
create table departments ( dept_id integer not null primary key, name varchar(100) not null ); create table employees ( emp_id integer not null primary key, dept_id integer not null, lastname varchar(100) not null firstname varchar(100), constraint fk_emp_dept foreign key (dept_id) references departments (dept_id) deferrable initially deferred );This allows for the following:
insert into employees (emp_id, dept_id, lastname, firstname) values (1, 42, 'Ford', 'Prefect'); insert into departments (dept_id, name) values (42, 'Space Pilots'); commit;It is also useful for self-referencing tables:
create table employees ( emp_id integer not null primary key, supervisor integer, lastname varchar(100) not null firstname varchar(100), constraint fk_emp_manager foreign key (supervisor) references employees (emp_id) deferrable initially deferred ); insert into employees (emp_id, supervisor, lastname, firstname) values (1, 42, 'Dent', 'Arthur'); insert into employees (emp_id, supervisor, lastname, firstname) values (42, null, 'Ford', 'Prefect'); commit;
Deferred constraints are something different than statement based constraint evaluation