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

问题描述

我有一个 post 表中有列 id，owner

我有另外一个表 action with columns user_id，post_id

表 user with columns id

post_id - > post.id 和 user_id - > user.id 也是 post.owner - > user.id

现在我想约束 post（post_id）.id！= user_id 在表格动作

这可能怎么样？

解决方案

不支持在CHECK约束中查看当前行之外的内容。 http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html 说：

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

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

这个限制有很好的理由，但是如果你喜欢在单轮独轮车上玩火炬手交通繁忙，你可以用功能来颠覆限制。这种情况不会回来咬你是罕见的;您可以更安全地执行触发器代码中的不变量。

http://www.postgresql.org/docs/9.1/interactive/triggers.html

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

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