using create constraint trigger

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-general(at)postgresql(dot)org
Subject: using create constraint trigger
Date: 2001-06-06 22:19:07
Message-ID: 20010606171907.A16460@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Neil Conway 2001-06-06 22:31:58 Re: Text data type doesn't accept newlines?
Previous Message Alex Pilosov 2001-06-06 22:14:54 Re: [HACKERS] something smells bad