添加检查总和的约束

发布于 2024-11-07 12:12:05 字数 781 浏览 0 评论 0原文

我试图通过检查值的总和是否 << 来向表添加约束。例如

这是我的架构：

  CREATE TABLE Works  (
     eid      INTEGER,
     did      INTEGER,
     pct_time INTEGER,
     PRIMARY KEY (eid,did),
     FOREIGN KEY (eid) REFERENCES Employee(eid),
     FOREIGN KEY (did) REFERENCES Dept(did)
  );

我需要检查每个 eid 的 pct_time 总和是否 <= 100。

，

eid --- did ---- pct_time
0 ----- a ------- 50
0 ----- d ------- 40
0 ----- c ------- 20
1 ----- a ------- 90
1 ----- b ------- 10
2 ----- d ------- 40
2 ----- a ------- 20

在这里，当我为 eid 0 添加第三个条目作为总和 > 时，应该会出错。 100.
对于 eid 1 和 2 来说就可以了，因为 pct_time <= 100
这怎么能做到呢？

到目前为止我所做的就是

ALTER TABLE Works
ADD CONSTRAINT c1
CHECK SUM(pct_time) < 100

原文

I'm trying to add a constraint to a table by checking that the sum of a value is < 100.

This is my schema:

  CREATE TABLE Works  (
     eid      INTEGER,
     did      INTEGER,
     pct_time INTEGER,
     PRIMARY KEY (eid,did),
     FOREIGN KEY (eid) REFERENCES Employee(eid),
     FOREIGN KEY (did) REFERENCES Dept(did)
  );

I need to check that the sum of pct_time for each eid is <= 100.

For example

eid --- did ---- pct_time
0 ----- a ------- 50
0 ----- d ------- 40
0 ----- c ------- 20
1 ----- a ------- 90
1 ----- b ------- 10
2 ----- d ------- 40
2 ----- a ------- 20

Here, it should have errored when I added the 3rd entry for eid 0 as the sum > 100.

It would have been fine for eid 1 and 2 as pct_time <= 100

How can this be done?

So far all I've done is

ALTER TABLE Works
ADD CONSTRAINT c1
CHECK SUM(pct_time) < 100

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小梨窩很甜 2024-11-14 12:12:05

您应该为此使用触发器。检查 SELECT SUM(pct_time) FROM Works GROUP BY eid 是否有插入后超过 100 的值。如果是这种情况，则抛出错误。

您熟悉触发器吗？您使用什么数据库系统？

编辑：
请在此处查看触发器文档。基本上你想要的是这样的：

CREATE TRIGGER Work_insert_trigger
BEFORE INSERT ON Works
FOR EACH ROW
EXECUTE PROCEDURE check_sum_work();

然后你的 check_sum_work() 过程将检查是否有任何 SUM(pct_time) > 。 100.

这里是触发器过程的文档，请注意NEW 特殊变量，允许您挑选出要插入的元素。因此，您需要SELECT SUM(pct_time) FROM Works WHERE eid = NEW.eid并查看该总和+NEW.pct_time是否大于100。

这听起来可能如果您以前从未见过触发器，那么这将非常复杂，但它实际上是一个非常强大的机制，您将学会欣赏。 :)

这里是使用 PostgeSQL 触发器的另一个示例。

You should use a trigger for this. Make it check if SELECT SUM(pct_time) FROM Works GROUP BY eid has any values that will go over 100 after your insert. If it is the case, throw an error.

Are you familiar with triggers? What database system are you using?

EDIT:
Check out the documentation for triggers here. Basically what you want is something like this:

CREATE TRIGGER Work_insert_trigger
BEFORE INSERT ON Works
FOR EACH ROW
EXECUTE PROCEDURE check_sum_work();

Then your check_sum_work() procedure will check if any SUM(pct_time) > 100.

Here is the documentation for trigger procedures, take note of the NEW special variable which allows you to single out the element being inserted. So you'll want to SELECT SUM(pct_time) FROM Works WHERE eid = NEW.eid and see if that sum + NEW.pct_time is greater than 100.

This might sounds pretty complicated if you've never seen triggers before, but it's actually a pretty powerful mechanism that you'll learn to appreciate. :)

Here is another example of PostgeSQL triggers being used.

回复收藏 0 原文

~没有更多了~