根据Postgres中另一个表中的整数列限制关系行数的最佳方法

Question

我有一个订阅计划表，其中包含许可证数量"座位"；对于该订阅，例如

CREATE TABLE plan (plan_id BIGSERIAL PRIMARY KEY, subscription_id BIGINT REFERENCES subscription "subscription_id", license_quantity INTEGER NOT NULL)

而目前的计划"；座位"；与计划表和用户之间的连接表一起使用，例如

CREATE TABLE plan_license (plan_id BIGINT NOT NULL REFERENCES plan "plan_id", user_id BIGINT NOT NULL REFERENCES "user" "user_id").

因此，如果license_quantity是3，并且plan_license中有一条记录将用户链接到该计划，那么就剩下两个许可证了。

确保关系行(许可证/席位(的数量不超过plan表中的license_quantity的最佳方法是什么？有更好的建模方法吗？

Answer 1

虽然我不太确定，如果这真的是最好的方式(它总是很难定义，它取决于很多点，甚至取决于你个人对它的定义：性能、存储、可用性、可维护性…(。我甚至不确定，如果我会这样做"商业逻辑"；直接在数据库中，而不是在后台。

然而，如果你真的想这样做，你可以构建一个BEFORE INSERT OR UPDATE触发器，它将在你插入数据之前执行：

演示：db<gt；小提琴

CREATE FUNCTION check_seats() RETURNS trigger AS $$
DECLARE
used_seats int;
BEGIN
SELECT COUNT(*)
FROM plan_license
WHERE plan_id = NEW.plan_id
INTO used_seats;

IF used_seats >= (
SELECT license_quantity 
FROM plan 
WHERE plan_id = NEW.plan_id
) THEN

RAISE EXCEPTION 'Too many seats for plan %', NEW.plan_id;

END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_seats 
BEFORE INSERT OR UPDATE ON plan_license
FOR EACH ROW EXECUTE PROCEDURE check_seats();

触发器函数查找当前座位数，如果座位数超过则抛出异常。在这种情况下，INSERT(或UPDATE(操作将被中止。

Answer 2

您可以使用带有自定义函数的check约束来实现此要求。

1. 编写如下自定义函数，该函数将根据您的条件返回Boolean

create or replace function check_val(planid bigint) returns boolean As $$
declare
plan_qty int;
licence_qty int;
begin
select license_quantity into plan_qty from plan where plan_id=planid;
select count(*) into licence_qty from plan_license where plan_id=planid;
return coalesce(plan_qty>licence_qty,false);
end;
$$
language plpgsql

2. 然后在带有检查约束的表定义中调用它

CREATE TABLE plan_license 
(
plan_id BIGINT NOT NULL REFERENCES plan "plan_id", 
user_id BIGINT NOT NULL REFERENCES "user" "user_id",
CONSTRAINT check_val_1 CHECK(check_val(plan_id))
)

如果你的表已经存在，那么你可以使用下面的查询来添加这个：

ALTER TABLE plan_license 
ADD CONSTRAINT check_val_1 
CHECK (check_val(plan_id));

演示