Re: Disable and enable of table and column constraints

Tom Lane wrote:
> Michael Gould <mgould(at)intermodalsoftwaresolutions(dot)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.