Thread: foreign key check

I have two tables t1, and t2 where t2.fid is a foreign key reference to
t1.id. Except in this case only a subset of the values in t1 are valid,
the values in t1 that are valid can be found by doing a:
SELECT t1.id JOIN t3 ON t3.id = t1.id
Is there some way to add this in as a check constraint? I could just
write a pl/pgsql function to perform a check before inserting, but I am
beginning to suspect that the reason I am running into this is because
my schema isn't accurately representing the business logic of the
application.

David Gardner <david@gardnerit.net> writes:
> I have two tables t1, and t2 where t2.fid is a foreign key reference to
> t1.id. Except in this case only a subset of the values in t1 are valid,
> the values in t1 that are valid can be found by doing a:
> SELECT t1.id JOIN t3 ON t3.id = t1.id
> Is there some way to add this in as a check constraint?

Nothing that would be reliable in the face of concurrent changes to
the third table --- unless you're willing to take out exclusive locks,
which will create performance issues as well as (probably) deadlock
problems.

I suggest thinking about how to refactor your schema to avoid this
situation.

            regards, tom lane