On Wed, 6 Jun 2001, Bruno Wolff III wrote:
> 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.
Yes. SQL does not allow you to reference against a set of columns that
aren't defined unique. We now enforce this in 7.1.
You could probably do the insert/update time check just by using the
function that checks insert for foreign keys. The referential actions are
more involved. The triggers need to make sure that there doesn't exist at
least one matching row before doing the action. This is actually needed
for other things as well and will probably be in the triggers for 7.2, but
you still couldn't define it as references due to the lack of unique.
As for how to use create constraint trigger, your best bet would be to
look at the output of pg_dump for the references constraints.
