Thread: The difference between RESTRICT and NO ACTION

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? Do both options
generate the same error message? Does "until later in the transaction"
mean that NO ACTION is not checked until an attempt is made to commit
the transaction?

If someone can point me to a more detailed explanation I would
appreciate it. Thanks.

Bill

Bill Todd wrote:
> 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? Do both
> options generate the same error message? Does "until later in the
> transaction" mean that NO ACTION is not checked until an attempt is
> made to commit the transaction?
>
> If someone can point me to a more detailed explanation I would
> appreciate it. Thanks.
>
> Bill
>
After posting the question I found a more complete explanation in the
manual.

Bill Todd <pg@dbginc.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