检查SQL Server中多行的约束 [英] Check constraint in SQL server multiple rows

问题描述

亲爱的，

我有一张桌子

Dear All,
I Have a table as

id	pid	per
1	1	40
2	1	60
3	2	30
4	2	40
5	2	60

和我想添加每个pid的约束（例如pid = 1然后每个的总和不应该超过100）

我怎么能添加这个约束。
$ b $对于pid = 2，b约束应该是暴力的，因为per的总和是（30 + 40 + 60）超过100.

我怎么能这样做

先谢谢



我的尝试：

and i want to add constraint that for each pid(e.g. pid=1 then sum of per should not be more than 100)
how can i add this constraint.
constraint should be violete for pid=2 because sum of per is (30+40+60) is more than 100.
How can i do this
Thanks in Advance

What I have tried:

i want to add constraint that for each pid(e.g. pid=1 then sum of per should not be more than 100)
how can i add this constraint.
constraint should be violete for pid=2  because sum of per is (30+40+60) is more than 100.
How can i do this
Thanks in Advance

推荐答案

不要。有一个CHECK约束，但是它不能复制像现有记录这样复杂的东西并与值进行比较。



有三种方法可以做到这一点： />
1）在尝试INSERT或UPDATE行之前在演示文稿软件中执行此操作。

2）在存储过程中执行此操作，而不是直接尝试INSERT或UPDATE。

3）使用TRIGGER检测值何时超过限制。



我更喜欢1，然后是2，并且会使用3不情愿。

Don't. There is a CHECK constraint, but it can't do complicated stuff like sum existing records and compare against the value.

There are three ways to do this:
1) Do it in your presentation software before you try to INSERT or UPDATE a row.
2) Do it in a stored procedure, instead of attempting an INSERT or UPDATE directly.
3) Use a TRIGGER to detect when the value exceeded a limit.

I'd prefer 1, then 2, and would use 3 with reluctance.

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