60

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 ?

Dipro Sen
  • 4,350
  • 13
  • 37
  • 50
  • 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? – SingleNegationElimination Apr 16 '12 at 18:33
  • So,IIUC if for instance the *action* is *upvote*, you basically want to prevent a user upvoting **their own** *post* ? – wildplasser Mar 20 '19 at 12:51

2 Answers2

96

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

kgrittn
  • 18,113
  • 3
  • 39
  • 47
  • 6
    Per [Tom Lane's comment](http://www.postgresql.org/message-id/13340.1271686859@sss.pgh.pa.us), 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._ – Shane Nov 11 '14 at 22:26
  • 3
    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. – guettli Feb 07 '17 at 12:20
  • 4
    "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. – kgrittn Mar 04 '17 at 21:07
  • 1
    Very useful! Out of curiosity, *why* would this end badly? – Abraham P Jul 25 '18 at 13:33
  • 9
    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. – kgrittn Jul 26 '18 at 19:31
0

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.

Saif-Alislam Dekna
  • 69
  • 1
  • 7