Postgres Pro
Downloads
Home   >   mailing lists

Re: Temporally disabled foreign key constraint check? - Mailing list pgsql-general

From raghu ram
Subject Re: Temporally disabled foreign key constraint check?
Date
Msg-id CALnrrJRmFSdee1p8Km8-2cCucSVvk8kzfEMVNBmgyHVuhCusdg@mail.gmail.com
Whole thread Raw
In response to Re: Temporally disabled foreign key constraint check?  (Emi Lu <emilu@encs.concordia.ca>)
List pgsql-general
Tree view


On Fri, Oct 21, 2011 at 9:19 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
Thank you first.

I believe that upate pg_class can only be done by superuser, right?

Yes,it's requires superuser privileges.
 

Besides, if I need the whole schema's foreign keys to be disabled and then enabled later.

Is there a simple command could do it? Similar to mysql's "set FOREIGN_KEY_CHECKS = false/true"?


No. The main goal for PG is to secure data integrity, and you can't switch off this feature.

Disable the constraints: 

alter table <tablename> disable trigger all; 

You can use this command inside your transaction too, but don't forget to reactivate the triggers because this change will become global after the transaction ends!

--Raghu
 
On 10/21/2011 11:12 AM, raghu ram wrote:


On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu <emilu@encs.concordia.ca
<mailto:emilu@encs.concordia.ca>> wrote:

   Good morning,


   Is there a way to temporally disabled foreign key constraints
   something like:

   SET FOREIGN_KEY_CHECKS=0

   When population is done, will set FOREIGN_KEY_CHECKS=1


You can disable *triggers* on a table (which will disable all the FK
constraints, but not things like 'not nul' or 'unique').

For Disable:

update pg_class set reltriggers=0 where relname = 'TEST';

For Enable:

update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='TEST';

--Raghu


--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
emilu@encs.concordia.ca        +1 514 848-2424 x5884

pgsql-general by date:

Previous
From: Joe Abbate
Date:
Subject: Re: copying few fields into an existing table
Next
From: raghu ram
Date:
Subject: Re: archive_timeout runtime change