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
