CODEWITHSUNDEEP
sqlpostgresqlconstraints
jeje
jeje

Reputation: 3211

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

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!

Upvotes: 37

Views: 16127

Answers (5)

Pourqavam
Pourqavam

Reputation: 80

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.

Upvotes: 0

flob
flob

Reputation: 126

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

Upvotes: 3

user80168
user80168

Reputation:

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.

Upvotes: 3

Aleksander Kmetec
Aleksander Kmetec

Reputation: 3327

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.

Upvotes: 37

Quassnoi
Quassnoi

Reputation: 425291

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)

Upvotes: 18

Related Questions