Temporally disabled foreign key constraint check?

On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu <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

raghu ram <raghuchennuru@gmail.com> hat am 21. Oktober 2011 um 17:12
geschrieben:

On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu<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';

 
 
 
No, don't manipulate pg_* - tables. Use instead ALTER TABLE ... DISABLE TRIGGER
...
 
 
Regards, Andreas 

Thank you first.

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

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"?

Emi

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

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