Как написать ограничение относительно максимального количества строк в postgresql?

Я думаю, что это довольно распространенная проблема.

У меня есть таблица user(id INT ...) и таблица photo(id BIGINT, owner INT). владелец является ссылкой на user(id).

Я хотел бы добавить ограничение на фотографию таблицы, которое предотвратило бы больше, чем позволить 10 фотографиям войти в базу данных для каждого пользователя.

Какой лучший способ написать это?

спасибо!

Ответ 1

Квасной прав; триггер будет лучшим способом для достижения этого.

Здесь код:

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

Я включил блокировку таблицы, чтобы избежать ситуаций, когда два одновременных tansactions будут считать фотографии для пользователя, см., что текущий счетчик равен 1 ниже предела, а затем оба вставки, что приведет к тому, что вы перейдете 1 за лимит. Если это вас не волнует, было бы лучше удалить блокировку, поскольку она может стать узким местом со многими вставками/обновлениями.

Ответ 2

Вы не можете записать такое ограничение в объявлении таблицы.

Есть некоторые обходные пути:

Создайте триггер, который будет проверять количество фотографий для каждого пользователя.
Создайте столбец photo_order, который сохранит порядок фотографий, сделайте (user_id, photo_order) UNIQUE и добавьте CHECK(photo_order BETWEEN 1 AND 10)

Ответ 3

Еще один подход - добавить столбец "photo_count" в таблицу users, обновить его с помощью триггеров, чтобы он отражал реальность, и добавить проверку на него, чтобы обеспечить максимальное количество фотографий.

Преимущество этого в том, что в любой момент мы знаем (без учета), сколько фотографий пользователь имеет.

С другой стороны, подход Quassnoi также довольно крут, так как он дает вам возможность изменять порядок фотографий в случае, если пользователь захочет его.