pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to temporally disable foreign key constraint check
5+ messages / 4 participants
[nested] [flat]
* how to temporally disable foreign key constraint check
@ 2011-10-21 13:36 Emi Lu <emilu@encs.concordia.ca>
2011-10-21 14:58 ` Re: how to temporally disable foreign key constraint check Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-10-21 17:04 ` Re: how to temporally disable foreign key constraint check Thomas Kellerer <spam_eater@gmx.net>
2011-10-21 23:51 ` Re: how to temporally disable foreign key constraint check Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 3 replies; 5+ messages in thread
From: Emi Lu @ 2011-10-21 13:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to temporally disable foreign key constraint check
2011-10-21 13:36 how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
@ 2011-10-21 14:58 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-10-21 15:47 ` Re: how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
2 siblings, 1 reply; 5+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2011-10-21 14:58 UTC (permalink / raw)
To: emilu@encs.concordia.ca; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to temporally disable foreign key constraint check
2011-10-21 13:36 how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
2011-10-21 14:58 ` Re: how to temporally disable foreign key constraint check Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
@ 2011-10-21 15:47 ` Emi Lu <emilu@encs.concordia.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Emi Lu @ 2011-10-21 15:47 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to temporally disable foreign key constraint check
2011-10-21 13:36 how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
@ 2011-10-21 17:04 ` Thomas Kellerer <spam_eater@gmx.net>
2 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2011-10-21 17:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: how to temporally disable foreign key constraint check
2011-10-21 13:36 how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
@ 2011-10-21 23:51 ` Craig Ringer <ringerc@ringerc.id.au>
2 siblings, 0 replies; 5+ messages in thread
From: Craig Ringer @ 2011-10-21 23:51 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-10-21 23:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-21 13:36 how to temporally disable foreign key constraint check Emi Lu <emilu@encs.concordia.ca>
2011-10-21 14:58 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2011-10-21 15:47 ` Emi Lu <emilu@encs.concordia.ca>
2011-10-21 17:04 ` Thomas Kellerer <spam_eater@gmx.net>
2011-10-21 23:51 ` Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox