Inserting new row to table using repository, no pk on table

Sara Eva 0
Viewed 34

I'm working with Oracle and table have no pk. On entity I'm using ROWID as Id

@Id
@Column(name="ROWID")
private String id;
@Column(name="phone_number")
private String phoneNumber;

All I want to do is to save a new row to table using save() method of repository. Here comes the problem. How can I skip generating id(rowid) for table and make oracle to do it by itself?

Same question, I added @GeneratedValue(strategy = GenerationType.IDENTITY) but getting error:

IdentityColumnSupportImpl does not support identity key generation

jpa oracle10g oracle spring-boot java
1 Answers

If you're really using Oracle 10g, it doesn't have identity column support. Also ROWID is a pseudo-column generated by Oracle for every row in a table, whether you specify it or not. It tracks the physical storage location of the row in the tablespace. Note that while ROWID is unique, it is NOT FIXED or indexed (it's more like a direct memory address) and should not be used as a primary key; you won't be able to use it as a reference to join to any other table.

In 10g, you'll have to go "old school" and build the identity capability piece by piece:

  1. Add a primary key column with a NUMBER data type
  2. Create a primary key constraint on the new column
  3. Create a sequence object to generate primary key values (note that values will be unique, but depending on your architecture and uptime requirements may not be ordered or consecutive)
  4. Create an ON INSERT FOR EACH ROW trigger for the table to get the next value from the sequence and replace the primary key value in the insert transaction

This is essentially what Oracle's "identity" column construct does behind the scenes, with system-generated sequences and triggers.

pmdba0
Related