Auto-increment 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 . The following figure shows an example. For each username : ( user1 , user2 ) user_item_number , form 1 starts and increases by 1.

I assume that I should use some trigger before insertion, which get the maximum user_item_number for username that is inserted and increment it. But I do not know how to write this trigger.

I also do not know how to consider access to the convention (concurency insert is larger than a few lines with the same username value). I don’t want to get a constraint violation error when two lines with the same username and user_item_number , I would like the trigger to catch this error and the other to increase the value of user_item_number .

Any idea?

Answer 1

I have found a solution. I wrote a trigger and a 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 promise allows you to create spaces, but that's fine for me.

I wanted a trigger to start instead of a trigger, but that is not possible. So I did before inserting the trigger and return null. Insertion is performed inside the procedure. Instruction:

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

avoid repetition

Answer 2

It is very difficult to create and maintain such a gapless sequence.

the best way to get the same results is to use window functions to generate such sequences on the fly. Sort of:

 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 the desired results and will not cause problems with concurrency. Also, it will always support sequences without spaces.

Answer 3

Auto inc must be unique and only one for the table.

So, based on what you think is necessary

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

where should you move in terms of normalization