Cannot persist a new record due to IDENTITY_INSERT is set to OFF

Hibernate ORM
1

I have a bit of an interesting problem. I’m working on a what is a somewhat legacy system and encountered the following error trying to persist a new record into a specific table:

Cannot insert explicit value for identity column in table ‘Table_Name’ when IDENTITY_INSERT is set to OFF.

I believe I found the root of the problem, what I believe is a limitation of hibernate, but a confirmation would be nice to ensure I’m not doing anything incorrectly.

The SQL Server table I’m working with looks something like this:

ID - Int, primary key
Qty - Decimal
Price - Decimal
Order_Sequence - Int, identity (+1)

Because Order_Sequence is an identity and not the primary key, hibernate does not accept the insert. I found this brief document that may confirm this:

http://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/#d0e1150

I tried marking Order_Sequence with @GeneratedValue and @GeneratedValue(strategy=“IDENTITY”) for but the error still occurs. Also setting IDENTITY_INSERT to ON before the inserting does not work.

Can someone confirm this is a limitation?

2

If the Order_Sequence column is the IDENTITY, then the Primary Key column must be manually assigned.

If that’s the case, then the ID column must be mapped like this:

@Id
@Column(name = "ID")
private Long id;

while the Order_Sequence column will be mapped as follows:

@Generated( value = GenerationTime.INSERT)
@Column(name = "Order_Sequence")
private Long orderSequence;

Try it like this and let me know how it works.

1 Like
3

That worked!

As I look at the documentation closer, @GeneratedValue is meant for primary key constraints only, which is why it was not working.

Thanks @vlad for your help.

4

You are very welcome.

5

Worked for me. Thanks @vlad