Обсуждение: how to temporally disable 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
Something like ALTER TABLE t_yourtable DROP CONSTRAINT and then ALTER TABLE t_yourtable ADD FOREIGN KEY ? Best, Oliveiros ----- Original Message ----- From: "Emi Lu" <emilu@encs.concordia.ca> To: <pgsql-sql@postgresql.org> Sent: Friday, October 21, 2011 2:36 PM Subject: [SQL] how to temporally disable 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 > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
Not really, sorry :( What I am looking for is no DB structure changes. But only disabling foreign key constraint check/verify for period of time and then recover it. Similar to mysql's "set FOREIGN_KEY_CHECKS = true/false" Emi On 10/21/2011 10:58 AM, Oliveiros d'Azevedo Cristina wrote: > Something like > > ALTER TABLE t_yourtable DROP CONSTRAINT > and then > ALTER TABLE t_yourtable ADD FOREIGN KEY > ? > > Best, > Oliveiros > > ----- Original Message ----- From: "Emi Lu" <emilu@encs.concordia.ca> > To: <pgsql-sql@postgresql.org> > Sent: Friday, October 21, 2011 2:36 PM > Subject: [SQL] how to temporally disable 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 >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 emilu@encs.concordia.ca +1 514 848-2424 x5884
Emi Lu wrote on 21.10.2011 15:36: > 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 > You can define the FKs as "DEFERRABLE INITIALLY IMMEDIATE". Then at the start of your transaction you can defer constraint checking until you commit everything using: SET CONSTRAINTS ALL DEFERRED; Thomas
On 10/21/2011 09:36 PM, Emi Lu 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 If you really, really want to do this you can do it by disabling the triggers that enforce the checks on particular tables. This must be done table-by-table, there's no global way to do it. Use ALTER TABLE ... DISABLE TRIGGER to do it. See: http://www.postgresql.org/docs/current/static/sql-altertable.html -- Craig Ringer