时间戳

首页 > 解决方案 > 如何在 Postgres 中为表中的行数限制建模

postgresql - 如何在 Postgres 中为表中的行数限制建模

问题描述

我目前正在学习如何在全栈 Web 开发环境中使用数据库。我想模拟一个场景，学生可以参加具有一定能力的各种活动。我已经查看了这个线程：

如何在postgresql中编写关于最大行数的约束？

但不能完全将其应用于我的场景，因为它应该有可能具有不同的容量，例如当房间更新时也会改变（容量引用房间表的容量列）

有没有一种优雅的方式来建模这个？

我目前的解决方案是有一个容量列和一个用于在每次插入/删除时获得/减少的注册的列。这种粗略的检查也会发生在客户端 atm 上，我知道这是一种不好的做法，原因有很多，我对此绝对不满意，这就是我创建这篇文章的原因。

我认为至少可以使用 Postgres 函数对服务器端进行建模，但由于我还不熟悉这些函数，所以我想先问一下，是否有更好的建模方法。我正在为我的项目使用 Supabase 实例。

提前谢谢大家！

标签： postgresqlcrudsupabase

解决方案

有很多方法可以做到这一点，但如果你只是想让 PostgreSQL 确保你永远不会超额预订活动，你可以为你的桌子写INSERT一个UPDATETRIGGER registration。

我在我的 Supabase 实例上对此进行了测试，因为没有什么比从 StackOverflow 获得无效的专家答案更烦人的了！

CREATE TABLE person (id INTEGER UNIQUE PRIMARY KEY, firstname TEXT, lastname TEXT);
CREATE TABLE event (id INTEGER UNIQUE PRIMARY KEY, event_name TEXT, capacity INTEGER);
CREATE TABLE registration (id INTEGER UNIQUE PRIMARY KEY, event_id INTEGER , person_id INTEGER);

INSERT INTO person (id, firstname, lastname) VALUES (1, 'Bob','Barker'),(2, 'Jamie','Oliver'),(3, 'Gary ','Busey');

INSERT INTO event (id, event_name, capacity) VALUES (1, 'Fireman''s Ball', 2);

CREATE OR REPLACE FUNCTION check_registration_capacity()
  RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT count(*) FROM registration as r1 WHERE r1.event_id = NEW.event_id) > 
     ((SELECT capacity FROM event WHERE event.id = NEW.event_id) - 1)
  THEN
    RAISE EXCEPTION 'Event is full';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER verify_registration_capacity 
BEFORE INSERT OR UPDATE ON registration 
FOR EACH ROW EXECUTE PROCEDURE check_registration_capacity();

-- register Bob for the Fireman's Ball
INSERT INTO registration (id, event_id, person_id) VALUES (1, 1, 1); 
-- register Jamie for the Fireman's Ball
INSERT INTO registration (id, event_id, person_id) VALUES (2, 1, 2);
-- sorry, Gary, the next line throws an 'Event is full' error
INSERT INTO registration (id, event_id, person_id) VALUES (3, 1, 3);

推荐阅读