Autoincrement separately for each foreign key

Question

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

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.

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?

Answer 1

This should help. A simpler option. Than the message accepted as an answer.

CREATE OR REPLACE FUNCTION set_internal_order() RETURNS trigger as
$$
DECLARE
    max_order INTEGER;
BEGIN
    IF NEW.internal_order IS NOT NULL THEN
        RETURN NEW;
    END IF;

    SELECT COALESCE(MAX(internal_order), 0) + 1 INTO max_order
    FROM landing
    WHERE project_id = NEW.project_id; -- project id is Frghn. key

    NEW.internal_order := max_order;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig1
    before insert
    ON landing
    FOR EACH ROW
EXECUTE PROCEDURE set_internal_order();

Answer 2

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

Answer 3

It is really hard to generate and maintain such gapless sequence.

the better way to obtain same results is to use window functions to generate such sequences on the fly. Something like:

SELECT id, username, row_number() OVER (PARTITION BY username ORDER BY id) as user_item_number, item_value
from item_table;

It will give you desired results and wont cause any problems with concurrency. Also it will always maintain sequences gapless.

Answer 4

Auto inc should be unique and only one per table.

So based on what you seem to want

User(User_ID PK, ...)
UserItem(User_Item_ID PK, User_ID FK, ...)
UserItemValue(User_Item_Value_ID PK, User_Item_ID FK, ...)

is where you should be heading from a normalisation point of view