Обсуждение: Temporally disabled foreign key constraint check?
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 Thanks a lot! Emi -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 emilu@encs.concordia.ca +1 514 848-2424 x5884
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 > > --Raghu > >
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
On 10/21/2011 11:12 AM, raghu ram wrote:<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