How to write a constraint concerning a max number of rows in postgresql?

Question

Hi,

I think this is a pretty common problem.

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.

What's the best way of writing this?

Thx!

Answer 1

You cannot write such a constraint in a table declaration.

There are some workarounds:

• Create a trigger that would check the number of photos for each user
• Create a photo_order column that would keep the order of photos, make (user_id, photo_order) UNIQUE, and add CHECK(photo_order BETWEEN 1 AND 10)

Answer 2

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

Here's the code:

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

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.

Answer 3

One another approach would be to add column "photo_count" to users table, update it with triggers to make it reflect reality, and add check on it to enforce maximum number of photos.

Side benefit from this is that at any given moment we know (without counting) how many photos user has.

On other hand - the approach Quassnoi suggested is also pretty cool, as it gives you ability to reorder the photos in case user would want it.