On 04/17/2012 07:43 AM, Igor Neyman wrote:
>> -----Original Message-----
>> From: Chris Ernst [mailto:cer...@zvelo.com]
>> Sent: Monday, April 16, 2012 10:55 PM
>> To: pgsql-admin@postgresql.org
>> Subject: Re: Recreate primary key without dropping foreign keys?
>> On 04/16/2012 07:02 PM, amador alvarez wrote:
>>> How about deferring the FK's while recreating the PK ?
>>> or using a temporary parallel table to be pointed by the other tables
>>> (FK) and swap it up on the recreation.
>> Hmm.. Interesting.   But it appears that you have to declare the
>> foreign
>> key as deferrable at creation.  Is there any way to set an existing
>> foreign key as deferrable?
>>      - Chris
> May be this (from the docs) would help:
> "ADD table_constraint [ NOT VALID ]
>     This form adds a new constraint to a table using the same syntax as 
> CREATE TABLE, plus the option NOT VALID, which is currently only allowed for 
> foreign key constraints. If the constraint is marked NOT VALID, the 
> potentially-lengthy initial check to verify that all rows in the table 
> satisfy the constraint is skipped. The constraint will still be enforced 
> against subsequent inserts or updates (that is, they'll fail unless there is 
> a matching row in the referenced table). But the database will not assume 
> that the constraint holds for all rows in the table, until it is validated by 
> using the VALIDATE CONSTRAINT option."
> Using this option you can drop and recreate corresponding FKs in a very short 
> time, and start using them, while postponing to run "VALIDATE CONSTRAINT" for 
> later.
> It's similar to Oracle's adding FK with "NOCHECK" option, but if IRC there is 
> no need to run "VALIDATE CONSTRAINT" later.

Hi Igor,

Oooooo... I like the sound of this.   I'll give this a shot in the test
environment and report back my findings.

Thanks a bunch!

        - Chris

Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:

Reply via email to