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
