Re: The difference between RESTRICT and NO ACTION - Mailing list pgsql-general

From Tom Lane
Subject Re: The difference between RESTRICT and NO ACTION
Date
Msg-id 4271.1233022978@sss.pgh.pa.us
Whole thread Raw
In response to The difference between RESTRICT and NO ACTION  (Bill Todd <pg@dbginc.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Bill Todd
Date:
Subject: Re: The difference between RESTRICT and NO ACTION
Next
From: Tom Lane
Date:
Subject: Re: What is the best plan to upgrade PostgreSQL from an ancient version?