databasepostgresql

Autoincrement separately for each foreign key


I have two tables:

 CREATE TABLE "user"
    (
      username character varying(35) NOT NULL,
      CONSTRAINT user_pk PRIMARY KEY (username)
    )
    CREATE TABLE item
    (
      id serial NOT NULL,
      username character varying(35),
      user_item_number integer,
      item_value character varying(35),
      CONSTRAINT item_pk PRIMARY KEY (id),
      CONSTRAINT item_fk FOREIGN KEY (username)
          REFERENCES "user" (username) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT unique_item_username UNIQUE (username, user_item_number)
    )

enter image description here

I would like to auto increment user_item_number separately for each username. Following image shows example. For each username: (user1, user2) user_item_number starts form 1 and is incremented by 1. enter image description here

I suppose that I should use some trigger before insert which get max value of user_item_number for username which is inserted and increment it. But I don't know how to write that trigger.

I also don't know how to consider a concurency access (concurency insert more than one row with the same value of username). I don't want to get constraint violation error when two rows with the same username and user_item_number are inserted, I would like that trigger catch that error and one again increment user_item_number value.

Any idea?


Solution

  • I found solution. I wrote trigger and procedure:

    create OR REPLACE function myinsert() RETURNS trigger as $$
        BEGIN
            if NEW.user_item_number is not null then return NEW;
            end if;
            loop
                <<roolbac_to>>
                declare
                    max INTEGER:=null;
                begin
                SELECT count(user_item_number) into max from item where username=NEW.username;
                if max is null then
                    max:=1;
                ELSE
                    max=max+1;
                end if;
                INSERT INTO item( username, user_item_number, item_value) VALUES (NEW.username,max, NEW.item_value);
                exit;
                exception WHEN unique_violation THEN
                --do nothing
                end;
            end loop;
        return null;
        end;
    
    $$ LANGUAGE 'plpgsql';
    
    CREATE TRIGGER trig1
     before insert 
        ON item
       FOR EACH ROW
    EXECUTE PROCEDURE myinsert();
    

    This sulution alows for gaps creation, but it is ok for me.

    I wanted to moke trigger instead of insert but it is impossible. So I made before insert trigger and return null. Insert is executed inside procedure. Instruction:

    if NEW.user_item_number is not null then return NEW;
        end if;
    

    is to not allow for reccurence