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