Re: Constraint to ensure value does NOT exist in another table?

I've dealt with something similar by using a check constraint and a stored procedure.  The check constraint calls a
storedprocedure, passing it (in your case) the key you want to make sure doesn't exist in some other table.  The stored
proceduresqueries 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
queryingis a ref table that is very static.  This approach may not be bullet proof for checking key relationships in
dynamictables.  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