如何写一个关于postgresql中最大行数的约束？ [英] How to write a constraint concerning a max number of rows in postgresql?

问题描述

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

I think this is a pretty common problem.

我有一个表 user（id INT ...）和表照片（id BIGINT，所有者INT）。 owner是用户（id）上的引用。

I've got a table user(id INT ...) and a table photo(id BIGINT, owner INT). owner is a reference on user(id).

我想为表格照片添加约束

I'd like to add a constraint to the table photo that would prevent more than let's say 10 photos to enter the database for each users.

这是最好的写法吗？

Thx！

推荐答案

Quassnoi是对的;

Quassnoi is right; a trigger would be the best way to achieve this.

这是代码：

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();

我包括表锁定，以避免两个并发tansaction计数用户的照片，当前计数是低于限制的1，然后两个插入，这将导致你超过限制1。如果这不是你的担心，最好删除锁定，因为它可能成为许多插入/更新的瓶颈。

I included table locking in order to avoid situations where two concurrent tansactions would count photos for a user, see that the current count is 1 below the limit, and then both insert, which would cause you to go 1 over the limit. If that's not a concern for you it would be best to remove the locking as it can become a bottleneck with many inserts/updates.

这篇关于如何写一个关于postgresql中最大行数的约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！