PG 9.1 - FK + Check constraint
Hi guys!
The column ent_id bigint on table table1 refers to other tables.
if ent_id = 1 then = table2.idif ent_id = 2 then = table3.idelse = no checks
I need a FK Constraint on table table1 checking if records on table2.id and
table3.idexists.
I know that currently, CHECK expressions cannot contain subqueries nor
refer to variables other than columns of the current row. Another solution
would be to create an IMMUTABLE functiondoing the check and use that in a
CHECK constraint [1]https://stackoverflow.com/questions/10135754/how-to-make-a-foreign-key-with-a-constraint-on-the-referenced-table-in-postgresq. However, I'm concern about doing this as I use
replication slaves, where all my selects are ran in there. I'm worried
about replication lag in this case.
Is there any other way to perform this? I'm using PG 9.1 for this.
[1]: https://stackoverflow.com/questions/10135754/how-to-make-a-foreign-key-with-a-constraint-on-the-referenced-table-in-postgresq
https://stackoverflow.com/questions/10135754/how-to-make-a-foreign-key-with-a-constraint-on-the-referenced-table-in-postgresq
Thanks!
Patrick
On Tue, Jul 18, 2017 at 6:56 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Another solution would be to create an IMMUTABLE functiondoing the check
and use that in a CHECK constraint [1].
Why do you need an FK constraint? Why can you not use a data modification
trigger?
Placing the subquery within a mis-defined immutable function so PostgreSQL
is oblivious to it doesn't do anything to overcome the fact that the system
is not designed to have check constraints with subqueries. You are likely
to get burned - commonly during backup restoration but there is no
guarantees as to why might happen.
David J.