我认为这是一个相当普遍的问题.
我有一张user(id INT ...)
号桌和一张photo(id BIGINT, owner INT)
号桌.所有者是user(id)
上的推荐人.
我想给表photo添加一个约束,防止每个用户有超过10张照片进入数据库.
写这个最好的方式是什么?
谢谢!
我认为这是一个相当普遍的问题.
我有一张user(id INT ...)
号桌和一张photo(id BIGINT, owner INT)
号桌.所有者是user(id)
上的推荐人.
我想给表photo添加一个约束,防止每个用户有超过10张照片进入数据库.
写这个最好的方式是什么?
谢谢!
卡西诺是对的;触发是实现这一目标的最佳方式.
以下是代码:
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.如果您不担心这一点,那么最好移除锁定,因为它可能会成为许多插入/更新的瓶颈.