Re: The difference between RESTRICT and NO ACTION

Bill Todd <pg(at)dbginc(dot)com> writes:
> In discussing foreign key constraints the manual makes the following
> statement about the difference between RESTRICT and NO ACTION.

> "(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.)"

> Can someone explain what this means in practical terms?

Well, you can defer a NO ACTION check until end of transaction.
RESTRICT will always be checked at end of statement. Which is also
the default behavior for NO ACTION, so I can see why you might not
initially notice any difference. See the DEFERRABLE and INITIALLY
DEFERRED options for foreign key constraints, and the SET CONSTRAINTS
command.

As for why you might *want* a deferred check, the only practical use
I can think of is to delete a referenced row in the master table, then
insert a replacement row with the same key, before ending the
transaction. In principle you could do that as a single UPDATE, but
it might be that your application logic makes it awkward to do so.

regards, tom lane