Re: pg_dump and ALTER TABLE / ADD FOREIGN KEY

On 2002.06.23 01:23 Christopher Kings-Lynne wrote:
> > Some have expressed that this could be quite slow for large
> databases,
> > and want a type of:
> >
> > SET CONSTRAINTS UNCHECKED;
> >
> > However, others don't believe constraints other than foreign keys
> > should go unchecked.
>
> Well, at the moment remember taht all that other SET CONSTRAINTS
> commands
> only affect foreign keys. However, this is a TODO to allow deferrable
> unique constraints.
>
> > Or would the below be more appropriate?:
> > ALTER TABLE tab ADD FOREIGN KEY .... TRUST EXISTING DATA;
>
> Maybe instead of TRUST EXISTING DATA, it could be just be WITHOUT
> CHECK or
> something that uses existing keywords?

WITHOUT CHECK doesn't sound right. 'Make a foreign key but don't
enforce it'.

WITHOUT BACKCHECKING, WITHOUT ENFORCING CURRENT, ...

Anyway you look at it it's going to further break loading pgsql backups
into another database. Atleast the set constraints line will be
errored out on most other DBs -- but the foreign key will still be
created.

SET FKEY_CONSTRAINTS TO UNCHECKED;

> Except if we could make all constraints uncheckable, then restoring a
> dump
> would be really fast (but risky!)

No more risky than simply avoiding foreign key constraints. A unique
key is a simple matter to fix usually, foreign keys are not so easy
when you get into the double / triple keys