disable and enable trigger all when a foreign keys

From: Emanuel Araújo <eacshm(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: disable and enable trigger all when a foreign keys
Date: 2019-07-12 13:46:53
Message-ID: CAMuTAkaqysXHaGgQH3NBv-Rh-01RDGJQEz6wyuGBpu+Y1kDNxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

This is a situation when we needed fill a new store data in a family env.

When I run "alter table a disable trigger all;" ... the foreign key
"a_id_b_fkey" was disabled to, its ok, but I needed run a load data into
tables "a"and "b" and table a exists a tuple that not exists in column id_b
-> (references b(id)).

When finished, the command "alter table a enable trigger all" was executed
but not alert or broken, why? Cause orphan record is there.

This behavior is common or when doing "enable trigger all" PostgreSQL
whould show me a error or a warning?

In this case I have to drop constraint and re-create again to get my goal.

-- Statements to reproduce
drop table if exists a;
drop table if exists b;
create table if not exists a (id int primary key , id_b int, descr text);
create table if not exists b (id int primary key , descr text);
alter table a add constraint a_id_b_fkey foreign key (id_b) references
b(id);
alter table a disable trigger all ;
insert into b values (1, 'house');
insert into b values (2, 'apartment');
insert into a values (1,1,'house sold');
insert into a values (2,1,'house 1 not sold');
insert into a values (3,2,'apartment 1 not sold');
insert into a values (4,null,'house to buy');
insert into a values (5,3,'car to sell');
select * from a;
select * from b;
alter table a enable trigger all;
select * from a;
select * from b;

--

*Atenciosamente,Emanuel Araújo*

*Linux Certified, DBA PostgreSQL*

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-07-12 14:04:25 Re: disable and enable trigger all when a foreign keys
Previous Message Dirk Mika 2019-07-12 11:36:46 Re: How to run a task continuously in the background