CODEWITHSUNDEEP
sqldatabasedatabase-designpostgresql
Mike Christensen
Mike Christensen

Reputation: 91666

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 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!

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".

Upvotes: 3

Views: 204

Answers (2)

onedaywhen
onedaywhen

Reputation: 57063

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.

Upvotes: 2

gbn
gbn

Reputation: 432431

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"

Upvotes: 1

Related Questions