On Sun, 19 Oct 2003, Sharon Cowling wrote:
> Ok I tried that, still no luck, i set tgdeferrable to 't' for all the
> constraints as you can see below. I then put the delete statement
> within a transaction that set all constraints to deferred, but still get
> a referential integrity violation.
>
> I ran this statement:
> taupo=# BEGIN;
> BEGIN
> taupo=# SET CONSTRAINTS ALL DEFERRED;
> SET CONSTRAINTS
> taupo=# delete from faps_key where substring(key_code from 1 for 1) = 'B';
> DELETE 400
> taupo=# COMMIT;
> ERROR: archivefapskey_keycode_fk referential integrity violation - key
> in faps_key still referenced from archive_faps_key
> taupo=#
>
> Still have all 400 entries.
You'd still need to insert the appropriate entries before the transaction
commits with deferral. It just means that the delete works so that you
can insert the new rows before committing, but at commit time the
constraint must be satisfied.
If you really want to not have the constraint fire until some later point
and you can't put everything in a single transaction, you're probably best
off with dropping the constraint and re-adding it. It'll be slow, but
it should guarantee that you didn't orphan any rows.
If you're willing to risk corruption and are a superuser, you can
temporarily turn off triggers for the table in question by setting
reltriggers to 0 for the pg_class row and then setting it back to the
correct value when you're done. This won't work if you have other
triggers/fk constraints that you want to have run, though.