Re: Disable and enable of table and column constraints

On Tue, Sep 8, 2009 at 1:07 PM, Alvaro
Herrera<alvherre@commandprompt.com> wrote:
> Tom Lane wrote:
>> Michael Gould <mgould@intermodalsoftwaresolutions.net> writes:
>> > It would be nice if we could enable and disable column and table
>> > constraints.  I believe that you can do this in Oracle but this is very
>> > handy for testing stored procedures and other external processes.
>>
>> Drop the constraint and re-add it later...
>
> That's not very useful when adding it later means grabbing an exclusive
> lock on the table for the whole duration of the full table scan required
> to check the table.
>
> Actually something in this area is on my plate too -- a customer of ours
> wants to be able to define constraints but not have it checked
> immediately.  I envision it similar to how concurrent index creation
> works: the constraint is created as "not checked" and the transaction is
> committed; new insertions are checked against the constraint.  Then the
> table is scanned to verify that extant tuples pass the constraint,
> _without_ the exclusive lock on the table.
>
> Both DB2 and Oracle have an ENFORCE setting for constraints, and a MySQL
> blog hinted some time ago that it might be in SQL 201x.
>
> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.

The mysql'ism foreign_key_checks would seem to do similar things...?
(http://dev.mysql.com/doc/refman/5.1/en/server-session-variables.html#sysvar_foreign_key_checks
)
--
Rob Wultsch
wultsch@gmail.com