postgresql 检查约束中的 SQL 子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10179121/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Sub queries in check constraint
提问by Dipro Sen
Can I make SQL sub queries in Check constraint ?
我可以在检查约束中进行 SQL 子查询吗？
I've a posttable with columns id, owner
I've another table actionwith columns user_id, post_id
Table userwith columns id
我有一个post带列的表id, owner
我有另一个action带列的user_id, post_id
表user带列的表id
post_id -> post.idand user_id -> user.idalso post.owner -> user.id
post_id -> post.id并且user_id -> user.id还post.owner -> user.id
Now I want to constraint post(post_id).id != user_idon table action
现在我想约束post(post_id).id != user_id表action
How is that possible ?
这怎么可能？
回答by kgrittn
It is not supported to look beyond the current row in a CHECK constraint.
不支持在 CHECK 约束中查看当前行之外的内容。
http://www.postgresql.org/docs/9.1/interactive/sql-createtable.htmlsays:
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.
Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.
指定为列约束的检查约束应仅引用该列的值，而出现在表约束中的表达式可以引用多个列。
目前，CHECK 表达式不能包含子查询，也不能引用当前行列以外的变量。
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 notcome 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
http://www.postgresql.org/docs/9.1/interactive/triggers.html
