Reverse FK constraint in Postgresql 9.0?

I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want t开发者_如何学JAVAo 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!

EDIT:

Let's say I have a table called "Names" :

1 Michael
2 David
3 William

Now I have a table called "Nicknames":

Mike -> 1
Mikey -> 1
Dave -> 2
Bill -> 3
Will -> 3

I want to make sure no one adds the row "Michael" to "Nicknames" since it already exists in "Names".

---

In Standard SQL you can use CREATE ASSERTION but PostgreSQL doesn't support it. You can fake it in triggers on both tables (e.g. UNION the two tables, GROUP BY names and test COUNT(*) > 1 or perhaps just test that the logical inserted table values do not appear in the other table) or otherwise procedural code.

You can 'design away' the problem by using one table and an explicit subtype and use a conventional UNIQUE constraint, as suggested by @gbn.

---

Have you considered using one table with

• "name type" (primar, nick name)
• "nickname of" which is a self FK

As I understand it, you have a list of names that should be unique.

This will remove the need for any code to maintain the "not FK"