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 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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Igor Neyman
Sent: Thursday, June 16, 2011 9:21 AM
To: Mike Christensen; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table?

-----Original Message-----
From: Mike Christensen [mailto:mike(at)kitchenpc(dot)com]
Sent: Thursday, June 16, 2011 1:05 AM
To: pgsql-general(at)postgresql(dot)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