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

Question

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

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.