首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何在postgresql中编写关于最大行数的约束?

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

Stack Overflow用户
提问于 2009-11-17 00:58:48
回答 3查看 10.7K关注 0票数 28

我认为这是一个很常见的问题。

我有一个表user(id INT ...)和一个表photo(id BIGINT, owner INT)。owner是user(id)上的引用。

我想向表photo添加一个约束,以防止每个用户超过10张照片进入数据库。

写这篇文章的最佳方式是什么?

谢谢!

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2009-11-17 01:46:26

Quassnoi是对的;触发器将是实现这一点的最好方法。

代码如下:

代码语言:javascript
复制
CREATE OR REPLACE FUNCTION enforce_photo_count() RETURNS trigger AS $$
DECLARE
    max_photo_count INTEGER := 10;
    photo_count INTEGER := 0;
    must_check BOOLEAN := false;
BEGIN
    IF TG_OP = 'INSERT' THEN
        must_check := true;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF (NEW.owner != OLD.owner) THEN
            must_check := true;
        END IF;
    END IF;

    IF must_check THEN
        -- prevent concurrent inserts from multiple transactions
        LOCK TABLE photos IN EXCLUSIVE MODE;

        SELECT INTO photo_count COUNT(*) 
        FROM photos 
        WHERE owner = NEW.owner;

        IF photo_count >= max_photo_count THEN
            RAISE EXCEPTION 'Cannot insert more than % photos for each user.', max_photo_count;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER enforce_photo_count 
    BEFORE INSERT OR UPDATE ON photos
    FOR EACH ROW EXECUTE PROCEDURE enforce_photo_count();

我包含了表锁,以避免两个并发事务会为用户计数照片的情况,看到当前计数比限制低1,然后两个insert都插入,这会导致超过限制1。如果这对您来说不是问题,那么最好删除锁定,因为它可能会成为许多插入/更新的瓶颈。

票数 33
EN

Stack Overflow用户

发布于 2009-11-17 02:06:52

另一种方法是将列"photo_count“添加到用户表中,使用触发器对其进行更新以使其反映实际情况,并对其添加检查以强制最大数量的照片。

这样做的另一个好处是,在任何给定的时刻,我们都知道(不计算)用户有多少照片。

另一方面,Quassnoi建议的方法也很酷,因为它让你能够重新排序照片,以防用户想要它。

票数 3
EN

Stack Overflow用户

发布于 2018-09-14 16:44:45

更好的替代方法是在执行插入操作时检查行数:

代码语言:javascript
复制
insert into photos(id,owner) 
select 1,2 from dual
where (select count(*) from photos where id=1) < 10
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1743439

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档