I am interested in having a constraint checked at the end of a transaction
that is close to a references constraint but not exactly the same.
Simplfied versions of the table create statements look like the following:
create table table1 (
col1 int primary key,
col2 int
);
create table table2 (
col1 int references table1,
col3 int,
col4 int,
primary key (col1, col3)
);
The additional constraint I want is that every row in table 1 should have
at least one corresponding row (but might have more than one) in table2
(with the same value of col1).
It looks like it might be possible to do this with create constraint trigger,
but the documentation for that command is sparse (and it warns against
using it).
It also seems like a deferred references constraint might work, but I am not
sure what the behavior will do what I want because of the duplicates in
the referenced table. The documentation says that uniqueness isn't enforced,
but doesn't directly describe what happens if one of several rows satisfying the
reference is deleted. It seems to imply if any of the referenced rows is
deleted then the delete action will occur, even if there are still other rows
that satisfy the restriction.
I can just enforce this constraint in the application, but I was hoping to
do it in the database.