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:
http://www.postgresql.org/mailpref/pgsql-admin
