Can I make SQL sub queries in Check constraint ?
I've a post table with columns id, owner
I've another table action with columns user_id, post_id
Table user with columns id
post_id -> post.id and user_id -> user.id also post.owner -> user.id
Now I want to constraint post(post_id).id != user_id on table action
How is that possible ?
It is not supported to look beyond the current row in a CHECK constraint.
http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html says:
A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns.
Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.
There are good reasons for this restriction, but if you like to juggle flaming torches while riding a unicycle through heavy traffic, you can subvert the restriction using functions. The situations in which this will not come back to bite you are rare; you would be much safer to enforce the invariant in trigger code instead.
http://www.postgresql.org/docs/9.1/interactive/triggers.html