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

user_127707 0
Viewed 12112

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!

constraints postgresql sql
5 Answers

A better alternative would be to check the number of rows when you do the insert:

insert into photos(id,owner) 
select 1,2 from dual
where (select count(*) from photos where id=1) < 10
user_6285880

I answered similar question here:

We can save the number of user photos in the user table or a table like user_statistics and use triggers to perform atomic increment and decrement that locks one row (user row) and is safe against concurrent requests:

CREATE TABLE public.user_statistics
(
    user_id integer NOT NULL,
    photo_count smallint NOT NULL DEFAULT 0,
    CONSTRAINT user_statistics_pkey PRIMARY KEY (user_id),
    CONSTRAINT user_statistics_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.user (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

CREATE FUNCTION public.increment_user_photo_count()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    updated integer;
BEGIN
    UPDATE
        user_statistics
    SET
        photo_count = photo_count + 1
    WHERE
        user_statistics.user_id = NEW.user_id AND user_statistics.photo_count < 10;
    GET DIAGNOSTICS updated = ROW_COUNT;
    IF updated = 0 THEN
        RAISE EXCEPTION 'a user can only have 10 photos';
    END IF;
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER photo_increment_user_photo_count
    BEFORE INSERT
    ON public.photo
    FOR EACH ROW
    EXECUTE PROCEDURE public.increment_user_photo_count();

CREATE FUNCTION public.decrement_user_photo_count()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    UPDATE
        user_statistics
    SET
        photo_count = photo_count - 1
    WHERE
        user_statistics.user_id = OLD.user_id;
    RETURN NULL;
    -- result is ignored since this is an AFTER trigger
END;
$BODY$;

CREATE TRIGGER photo_decrement_user_photo_count
    AFTER DELETE
    ON public.photo
    FOR EACH ROW
    EXECUTE PROCEDURE public.decrement_user_photo_count();

Instead of triggers we can update the photo_count like above in a transaction at application side and throw exception (rollback) for the increment if no rows affected by the update.

user_98857580
Related