Use prepare statement for Oracle Db, get unique constraint violated

Question

We try to use java preparedstatement to do insert action, we got ORA-00001: unique constraint (POSTIONS_TABLE_PK) violated error.

The app target at Oracle DB. the table name info:

postion_table columns:

 position_id number pk,
 amount number,
 account_id number

We need postion_id to be sequential number. start from 1. so will be 1, 2 ,3 .... n. For example, when we delete record with position_id 10, then the next new insert need to use 10 as position_id.

The insert query for prepared statement looks like:

BEGIN
DECLARE
position_id number;
BEGIN
  SELECT NVL(MAX(t.position_id) + 1, 1)
    INTO position_id
    FROM  positions_table t
   WHERE account_id = 1;
INSERT into positions_table(position_id,amount, account_id)
 values(position_id, ?, ?);
EXCEPTION
  WHEN OTHERS THEN 
    raise_application_error(-20999, 'Error : ' || sqlerrm);
    ROLLBACK;
END;
END;

We got unique constraint (TPOSTIONS_TABLE_PK) violated error when we try to insert multiple records in a batch.

Any suggestion on how to build the prepared SQL.

You have to commit at each record. Or you have to loop to generate max id for each record. Also you are using NVL function to default to 1, which will again create the same id in case of null — Jim Macaulay
for the above code you MUST ensure 'one' operation at a time, 2 runs at same time will hit exception, that's not really good thing to control. I would rather keep the PK based on a SEQUENCE (always increasing) and see if rownum (sorted by insert time) can be inferred as actual position — PrasadU

Connor McDonald Connor McDonald · Accepted Answer · 2020-07-23T07:38:13

Using

SELECT NVL(MAX(t.position_id) + 1, 1)

will never work in any system where you have more than a single user. Because two users will both run the command, they will both find POSITION_ID to be (say) 17, and they will both then try use 18, and one will fail.

Use a sequence (which might give you gaps) but then when you wish to get a list of rows with a gap-free number, do it at query time, ie

select row_number() over ( partition by ... order by position_id )

If you really really really need a gap free sequence generated at INSERT time, then a reasonably scalable method is:

create table with all the numbers (eg, 1 through 1,000,000) and index that column.
each person wanting a sequence does

open cursor for select ... from that_table for update skip locked;

and fetches a row from it.

Over time, every number will be used. But its a lot of work and complexity for something that is often not really a business requirement.

Use prepare statement for Oracle Db, get unique constraint violated

1 Answers