检查约束中的 SQL 子查询 [英] SQL Sub queries in check constraint

问题描述

我可以在 Check 约束中进行 SQL 子查询吗?

Can I make SQL sub queries in Check constraint ?

我有一个 post 表，其中包含 id, owner
列我有另一个表 action 列 user_id, post_id
表 user 与列 id

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 和 user_id ->user.id 也是 post.owner ->user.id

现在我想在 action

这怎么可能?

推荐答案

不支持在 CHECK 约束中超越当前行.

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 说:

指定为列约束的检查约束应引用仅该列的值，而表中出现的表达式约束可以引用多列.

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.

目前，CHECK 表达式不能包含子查询，也不能引用当前行的列以外的变量.

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

这篇关于检查约束中的 SQL 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！