Difference between RESTRICT and NO ACTION

Question

From postgresql documentation:

RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.)

Lets check it. Create parent and child table:

CREATE TABLE parent (   id serial not null,   CONSTRAINT parent_pkey PRIMARY KEY (id) );  CREATE TABLE child (   id serial not null,   parent_id serial not null,   CONSTRAINT child_pkey PRIMARY KEY (id),   CONSTRAINT parent_fk FOREIGN KEY (parent_id)     REFERENCES parent (id)      ON DELETE NO ACTION     ON UPDATE NO ACTION ); 

Populate some data:

insert into parent values(1); insert into child values(5, 1); 

And test does check is really deffered:

BEGIN; delete from parent where id = 1; -- violates foreign key constraint, execution fails delete from child where parent_id = 1; COMMIT; 

After first delete integrity was broken, but after second it would be restored. However, execution fails on first delete.

Same for update:

BEGIN; update parent set id = 2 where id = 1; -- same as above update child set parent_id = 2 where parent_id = 1; COMMIT; 

In case of deletes I can swap statements to make it work, but in case of updates I just can't do them (it is achivable via deleting both rows and inserting new versions).

Many databases don't make any difference between RESTRICT and NO ACTION while postgres pretends to do otherwise. Is it (still) true?

Answer 1

The difference only arises when you define a constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode.

See SET CONSTRAINTS.