SQL Sub queries in check constraint

Question

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 ?

Spoilage

Why should it matter if a column that references one table happens to have a value that is present in another table? What's the problem you're trying to solve? – Sepulchral

So,IIUC if for instance the action is upvote, you basically want to prevent a user upvoting their own post ? – Romanaromanas

Answer 1

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

Needful

Per Tom Lane's comment, hacking around the subquery restriction is not advised: "CHECK is meant to handle constraints on a row's value in isolation. If you try to use it to enforce cross-row conditions, the project will certainly end badly. – Waw

Yes, this is true: "CHECK is meant to handle constraints on a row's value in isolation." ... But: what is a unique constraint? Is it "juggle flaming torches while riding a unicycle through heavy traffic"? BTW I already tried both juggling flaming torches and riding a unicycle. Up to now I can't do both at one, but maybe in the future. – Blarney

"It is not supported to look beyond the current row in a CHECK constraint." Obviously, other types of constraints (i.e., UNIQUE, PRIMARY KEY, FOREIGN KEY, EXCLUDE) must look beyond the single row. – Needful

Very useful! Out of curiosity, why would this end badly? – Doxia

The reason that a cross-row reference in a CHECK constraint would end badly is that the constraint is only evaluated for the modified row. So, while it would generally work at first, when the other referenced row was modified in a way that invalidated the constraint, that would not be noticed. – Needful

Answer 2

A better approach is to use a before insert/update trigger that does the check, and if it fails it should raise an exception, which will roll-out the whole insertion/update... if it passes, then the insertion/update is executed.

Bacteriology