Constraint to ensure value does NOT exist in another table?

On Thu, Jun 16, 2011 at 6:04 AM, Mike Christensen <mike@kitchenpc.com> wrote:

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key..  Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?

The most common constraints are provided for, but then after that you
have to use triggers.

PostgreSQL deliberately doesn't support queries in CHECK constraints
for this reason.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key..  Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?

The most common constraints are provided for, but then after that you
have to use triggers.

PostgreSQL deliberately doesn't support queries in CHECK constraints
for this reason.

Thanks! I wrote a Trigger for this and it seems to work fairly well..
I kinda figured that was the obvious way, but thought there might be
some new 9.x feature that made this sort of thing possible.

-----Original Message-----
From: Mike Christensen [mailto:mike@kitchenpc.com]
Sent: Thursday, June 16, 2011 1:05 AM
To: pgsql-general@postgresql.org
Subject: Constraint to ensure value does NOT exist in another table?

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Mike

Trigger (with corresponding trigger function) will definitely do the
job.

Regards,
Igor Neyman

I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not).

I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamic tables. I'll let others speak to that.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Igor Neyman
Sent: Thursday, June 16, 2011 9:21 AM
To: Mike Christensen; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

-----Original Message-----
From: Mike Christensen [mailto:mike@kitchenpc.com]
Sent: Thursday, June 16, 2011 1:05 AM
To: pgsql-general@postgresql.org
Subject: Constraint to ensure value does NOT exist in another table?

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Mike

Trigger (with corresponding trigger function) will definitely do the
job.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 16 June 2011 14:41, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I've dealt with something similar by using a check constraint and a stored procedure.  The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table.  The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not).

I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static.  This approach may not be bullet proof for checking key relationships in dynamic tables.  I'll let others speak to that.

Did you use explicit locking? If not, you likely have a race
condition. The same applies to any sort of enforcement of business
rules inside triggers (or, indeed, check constraints). Check
constraints are generally intended to enforce simple, immutable rules
(i.e. that only reference the tuple that the rule is enforced on). I
would have used a trigger instead.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

I preferred the check constraint mainly because of the feedback "violation" messages. Other, more traditional constraints exist on other columns of the table. The names of those constraints contain information about the nature of the violation. I wanted to standardize the way I detected these violations and parse out the information. So I just added check constraints with names that followed the naming convention and always look for traditional constraint violation messages in my perl/DBI script.

No, no explicit locking, but as I said, the table being querried is static.

-----Original Message-----
From: Peter Geoghegan [mailto:peter@2ndquadrant.com]
Sent: Thursday, June 16, 2011 9:56 AM
To: Gauthier, Dave
Cc: Igor Neyman; Mike Christensen; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

On 16 June 2011 14:41, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I've dealt with something similar by using a check constraint and a stored procedure.  The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table.  The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not).

I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static.  This approach may not be bullet proof for checking key relationships in dynamic tables.  I'll let others speak to that.

Did you use explicit locking? If not, you likely have a race
condition. The same applies to any sort of enforcement of business
rules inside triggers (or, indeed, check constraints). Check
constraints are generally intended to enforce simple, immutable rules
(i.e. that only reference the tuple that the rule is enforced on). I
would have used a trigger instead.

--
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

On 16 Jun 2011, at 7:04, Mike Christensen wrote:

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4dfa49a012091645294739!

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key..  Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code.

You'd still have to use a TRIGGER to insert any new or updated values
into the third table. Otherwise, you'd have to modify a bunch of code
to insert/update the keys into the third table and that somewhat goes
against the whole idea of making the database responsible for its own
integrity in the first place.

What I'm ideally looking for here is a way to ensure the DB cannot
possibly exist in this state. Foreign keys let me do that, a trigger
(if written correctly) kinda does too so long as the data started out
in a valid state and the trigger is always run..

On 16 Jun 2011, at 20:47, Mike Christensen wrote:

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code.

You'd still have to use a TRIGGER to insert any new or updated values
into the third table. Otherwise, you'd have to modify a bunch of code
to insert/update the keys into the third table and that somewhat goes
against the whole idea of making the database responsible for its own
integrity in the first place.

No you don't.

If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to Table3.ColA, you can put a unique constraint on Table3.ColA to make sure the values are unique:
Table1 Table3 Table2
------ ------ ------
ColA >-------|- ColA -|-------< ColA

If you insert a value in either Table1 or Table2, it first HAS to exist in Table3, due to the FK constraints. However, that still allows for values that are in both tables 1 and 3, just pointing to the same value in Table3.

To solve that you add an extra column to all tables, for example:
ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A';
ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B';
And you change the FK constraints in A and B to include "src":

Table1 Table3 Table2
------ ------ ------
ColA >-------|- ColA -|-------< ColA
src >-/ \-|- src -|-/ \-< src

You also add back a UNIQUE constraint over Table3.ColA (without the "src" column).

Now, if you add a value to Table1, it requires a value of (ColA, 'A') in Table3. If you add one to Table2, it requires a value of (ColA, 'B'). If either of those already exist though, you violate the UNIQUE constraint on Table3.ColA.

It's probably convenient to write some triggers to auto-generate the records in Table3, but those triggers are NOT needed for relational integrity - they just make the task easier.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4dfaf78612091994554093!

I know I can setup a FK constraint to make sure Table1.ColA exists in
Table2.Key, however what if I want to do the reverse?

I want to ensure Table1.ColA does NOT exist in Table2.Key..  Can I do
this with any sort of CHECK constraint, trigger, custom function, etc?
Thanks!

Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code.

You'd still have to use a TRIGGER to insert any new or updated values
into the third table.  Otherwise, you'd have to modify a bunch of code
to insert/update the keys into the third table and that somewhat goes
against the whole idea of making the database responsible for its own
integrity in the first place.

No you don't.

If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to Table3.ColA, you can put a unique constraint on Table3.ColA to make sure the values are unique:
       Table1          Table3          Table2
       ------          ------          ------
        ColA >-------|- ColA -|-------< ColA

If you insert a value in either Table1 or Table2, it first HAS to exist in Table3, due to the FK constraints. However, that still allows for values that are in both tables 1 and 3, just pointing to the same value in Table3.

To solve that you add an extra column to all tables, for example:
       ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A';
       ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B';
And you change the FK constraints in A and B to include "src":

       Table1          Table3          Table2
       ------          ------          ------
        ColA >-------|- ColA -|-------< ColA
        src  >-/   \-|- src  -|-/   \-< src

You also add back a UNIQUE constraint over Table3.ColA (without the "src" column).

Now, if you add a value to Table1, it requires a value of (ColA, 'A') in Table3. If you add one to Table2, it requires a value of (ColA, 'B'). If either of those already exist though, you violate the UNIQUE constraint on Table3.ColA.

It's probably convenient to write some triggers to auto-generate the records in Table3, but those triggers are NOT needed for relational integrity - they just make the task easier.

Yup yup, I see where you're going.. It's like the third table is a
"name broker" that grants the unique priveledge of using a name in the
database. The other tables will have a FK on it so you'd have to add
that name to the table before it can be inserted elsewhere. The third
table will be unique which ensures a name is only used once.

This would work great, however I'd have to modify a bunch of code to
insert a name into the third table before it could be used.. Since an
admin tool is the only thing that would be doing this (this data
hardly ever changes), this isn't out of the question. I actually
don't need any TRIGGERS if I do this, I just need to modify some code.
This design will ensure my data is always in a valid state.

A fine approach. Thanks!

Mike