Problem with auto-generated primary_key using jpa on DerbyDB

Question

I have jpa annotated entity class like:

@Configurable
@Entity
@Table(name="PLAYERS")
public class Player
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="ID")
    private Integer id;
    @Column(name="NAME")
    private String name;

    @PersistenceContext
        public transient EntityManager entityManager;

    ...
}

This has worked fine until I decided to create table with backuped yaml data using syntax like:

createNativeQuery("INSERT INTO PLAYERS ...")

After successful creation when I try to create an entity with:

Player player = new Player();
player.setName("new player");
player.persist();

i got error:

SQL Error: -1, SQLState: 23505 

related to the duplication of primary_keys, because id generated for new entity = 1 (the same as row retrived from backuped data). Of course I can retrive data from backup file by using jpa/java syntax but in this case I have no control over primary keys of inserted data etc. How to solve this problem ? Is there any way to update id_generator after the insertion of backuped data ?

Answer 1

Because you inserted data manually, you need to alter the table to change the start value of the identity column:

ALTER TABLE PLAYERS ALTER COLUMN ID RESTART WITH 1234;

Where 1234 is the max id of your backup data.

More details in the documentation of the ALTER TABLE statement:

RESTART WITH integer-constant specifies the next value to be generated for the identity column. RESTART WITH is useful for a table that has an identity column that was defined as GENERATED BY DEFAULT and that has a unique key defined on that identity column. Because GENERATED BY DEFAULT allows both manual inserts and system generated values, it is possible that manually inserted values can conflict with system generated values. To work around such conflicts, use the RESTART WITH syntax to specify the next value that will be generated for the identity column. Consider the following example, which involves a combination of automatically generated data and manually inserted data:

CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
CREATE UNIQUE INDEX tautoInd ON tauto(i)
INSERT INTO tauto(k) values 1,2

The system will automatically generate values for the identity column.?? But now you need to manually insert some data into the identity column:

INSERT INTO tauto VALUES (3,3)
INSERT INTO tauto VALUES (4,4)
INSERT INTO tauto VALUES (5,5)

The identity column has used values 1 through 5 at this point.?? If you now want the system to generate a value, the system will generate a 3, which will result in a unique key exception because the value 3 has already been manually inserted.????To compensate for the manual inserts, issue an ALTER TABLE statement for the identity column with RESTART WITH 6:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6