ORA-00001: Unique Constraint: Setting Primary Keys Manually

Question

we have an Oracle Database and we have a table where we store a lot of data in. This table has a primary key and usually those primary keys are just created upon insertion of a new row.

But now we need to manually insert data into this table with certain fixed primary keys. There is no way to change those primary keys.

So for example:

Our table has already 20 entries with the primary keys 1 to 20. Now we need to add data manually with the primary keys 21 to 23.

When someone wants to enter a row using our standard approach, the insert process will fail because of:

Caused by: java.sql.BatchUpdateException: ORA-00001: Unique Constraint (VDMA.SYS_C0013552) verletzt

    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10500)
    at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:230)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)

I totally understand this: The database routine (sequence) that is creating the next primary key fails because the next primary key is already taken.

But: How do I tell my sequence to look at the table again and to realize that the next primary key is 24 and not 21 ?

UPDATE

The reason why the IDs need to stay the same is because is accessing the records using a Web Interface using links that contain the ID. So either we change the implementation mapping the old IDs to new IDs or we keep the IDs in the database.

UPDATE2

Found a solution: Since we are using hibernate, only one sequence is populating all the tables. Thus the primary keys in those 4 days where I was looking for an answer went so high that I can savely import all the data.

"Now we need to add data manually with the primary keys 21 to 23" - don't do that. Use the sequence to insert those values. Everything else will get you in trouble. There is no way you can make the sequence "look" at the table. The only way to change a sequence value is to call nextval or drop and re-create the sequence with a new start value. — a_horse_with_no_name
Advance the sequence, once, to the next available ID based on the maximum value you manually inserted. — Jeffrey Kemp
I agree with @a_horse_with_no_name. This is a fundamentally flawed design. If you are using a sequence to generate PK, then there is no inherent meaning in the value of any particular PK and thus zero reason to allow any particular value to be inserted 'manually'. — EdStevens
While I agree with a_horse and EdStevens in principle, I also know often you have to play the hand you are dealt. I wish I always got Aces in the hole; sadly, it ain't gonna happen. In your case, if you really only have three rows to add to your table, can you strip the PK value, add the rows one by one, and let the sequence populate the PK value with the values you wanted anyway? This assumes you are not in a multi-user environment... but if you are, adding those rows by hand while someone else added rows using the auto sequence will lead to collisions anyway. — mathguy

NP-complete NP-complete · Accepted Answer · 2016-05-21T14:52:57

How do I tell my sequence to look at the table again and to realize that the next primary key is 24 and not 21 ?

In Oracle, a sequence doesn't know that you intend to use it for any particular table. All the sequence knows is its current value, its increment, its maxval and so on. So, you can't tell the sequence to look at a table, but you can tell your stored procedure to check the table and then increment the sequence beyond the maximum val of the primary key. In other words, if you really insist on manually updating the primary key with non sequence values, then your code needs to check for non sequence values in the PK and get the sequence up to speed before it uses the sequence to generate a new PK.

Here is something simple you can use to bring the sequence up to where it needs to be:

select testseq.nextval from dual;

Each time you run it the sequence increments by 1. Stick it in a for loop and run it until testseq.currval is where you need it to be.

Having said that, I agree with @a_horse_with_no_name and @EdStevens. If you have to insert rows manually, at least use sequence_name.nextval in the insert instead of a literal like '21'. Like this:

 create table testtab (testpk number primary key, testval number);

 create sequence testseq start with 1 increment by 1;

 insert into testtab values (testseq.nextval, '12');
 insert into testtab values (testseq.nextval, '123');
 insert into testtab values (testseq.nextval, '1234');
 insert into testtab values (testseq.nextval, '12345');
 insert into testtab values (testseq.nextval, '123456');

 select * from testtab;

 testpk  testval
 2  12
 3  123
 4  1234
 5  12345
 6  123456

ORA-00001: Unique Constraint: Setting Primary Keys Manually

1 Answers