时间戳

首页 > 解决方案 > 根据多个关系中的条件限制关系中的条目数

sql - 根据多个关系中的条件限制关系中的条目数

问题描述

我正在使用 PostgreSQL，并试图限制学生可以拥有的并发贷款数量。为此，我创建了一个 CTE，它选择按 StudentID 分组的所有未归还贷款，并计算每个 StudentID 的未归还贷款数量。然后，我试图创建一个检查约束，使用该 CTE 将学生最多可以拥有的并发贷款数量限制为 7。

下面的代码不起作用，因为它在语法上无效，但希望它可以传达我想要实现的目标。有谁知道我可以如何实施我想要的贷款限制？

CREATE TABLE loan (
   id SERIAL PRIMARY KEY,
   copy_id INTEGER REFERENCES media_copies (copy_id),
   account_id INT REFERENCES account (id),
   loan_date DATE NOT NULL,
   expiry_date DATE NOT NULL,
   return_date DATE,

   WITH currentStudentLoans (student_id, current_loans) AS
   (
      SELECT account_id, COUNT(*)
      FROM loan
      WHERE account_id IN (SELECT id FROM student)
         AND return_date IS NULL
      GROUP BY account_id
   )

   CONSTRAINT max_student_concurrent_loans CHECK(
      (SELECT current_loans FROM currentStudentLoans) BETWEEN 0 AND 7 
   )
);

对于其他（和可选的）上下文，我包含了我的数据库模式的 ER 图。 在此处输入图像描述

标签： sqlpostgresql

解决方案

您不能使用这样的内联 CTE 来执行此操作。你有几个选择。

第一个是 UDF 和check约束。本质上，CTE 中的逻辑放在 UDF 中，然后check约束验证数据。

第二个是对这个表进行检查的触发器。但是，这很棘手，因为计数在同一张桌子上。

第三个是将总数存储在另一个表中 - 可能accounts- 并在此表上保持inserts、updates 和deletes 的最新状态。使该值保持最新需要在loans. 然后，您可以将check约束放在accounts.

我不确定哪种解决方案最适合您的整体架构。第一个最接近你现在正在做的事情。第三个“发布”计数，因此更清楚发生了什么。

推荐阅读