18

I receive the following exception when I'm trying to alter my @ID in an @Entity.

identifier of an instance of com.google.search.pagerank.ItemEntity was altered from 1 to 2.

I know that I'm altering the primary key in my table. I'm using JPA-annotations.

I solved this by using this single HQL query: update Table set name=:newName where name=:oldName

Instead of using the more OO approach: 

beginTransaction();
T e = session.load(...);
e.setName(newName);
session.saveOrUdate(e);
commit();

Any idea what the diff is?

naXa stands with Ukraine
  • 35,493
  • 19
  • 190
  • 259
  • Are you trying to use another entity as PK? – Miguel Ping Apr 09 '09 at 14:53
  • now im trying to change my the private key from a persitent entity. in sql it would be something like: "update Customers set customerId = 1492 where customer_id = 42;" and customer_id is my private key – Schildmeijer Apr 09 '09 at 20:19

4 Answers4

22

Actually, according to the JPA specification it is forbidden to change a primary key:

The application must not change the value of the primary key[8]. The behavior is undefined if this occurs.[9]

(from EJB 3 persistence (JPA) specification, paragraph 2.1.4)

Nir
  • 2,051
  • 2
  • 14
  • 6
9

I can't imagine why you'd want to do that. At all. Why would you change an entity's identity? You'd also need to update all the foreign keys in other tables that point to it. Seems like a pain, with no gain. You're probably better off making this a "business key" (plain property) and using a more permanent surrogate key. I have a feeling that you're going about this all wrong, but if you insist...

Essentially what you're doing is creating a new Customer and deleting the old one, and that's how I'd accomplish it in Hibernate.

[pseudocode]

Begin Transaction

// create new customer from old
newC = Session.Load<Customer>(42)
Session.Evict(newC)
newC.Id = 1492
Session.Save(newC)

// update other relationships to point to newC
// ....

// delete old customer
oldC = Session.Load<Customer>(42)
Session.Delete(oldC)

Commit Transaction

However, you're probably better off just doing it in all at once in a plain single SQL transaction, and in either case you risk having parallel processes that already have an instance of the "old" Customer, which might cause some errors.

Winston Fassett
  • 3,500
  • 3
  • 36
  • 29
  • 8
    First of all; there are no foreign key constraints to this identity. And ofcourse there are situations when one wants to change the primary key. I can admit that it's not a thing you do every day, but the context can without any doubt create a situation that involes it. – Schildmeijer Apr 10 '09 at 19:32
  • Although I can't *imagine* such a scenario, I acknowledge that one might exist. I would *love* to know what this scenario is. Also, any thoughts on the proposed solution? – Winston Fassett Apr 10 '09 at 20:28
  • my intentions are not to be rude. my scenario is something like this. I have a table with two columns, nick::string and number::int, where the nick is the primary key. And because of embedded memory constraints we cant afford to add a id like nickId. And people are allowed to change nick – Schildmeijer Apr 10 '09 at 21:12
  • 1
    your suggestion worked fine, but i ended up with a single HQL statement "update nick set nick = :oldNick where nick = : newNick" – Schildmeijer Apr 10 '09 at 21:14
  • but i still dont understand why i had to use HQL instead of entity.setNick(newNick); session.saveOrUpdate(entity); – Schildmeijer Apr 10 '09 at 21:15
  • 1
    To Hibernate, identifiers are immutable and changing them can mess up collections, proxies, and misc other stuff. So it throws an error, and even if it didn't, the ID is always excluded from generated UPDATE statements. So you have to do the stuff above, or just update via SQL or HQL. – Winston Fassett Apr 11 '09 at 15:03
  • the most common situation is when database is migrated from mid 80-ies / early 90-s/ not-RDBMS database. And refactoring it is a big pain with nearly no gains due "If it ain't broke, don't fix it" axiom. – Bogdan Aug 05 '16 at 07:49
  • There are many situations that you might need to update a primary key. One example of that is this: imagine you create a table call department and it can be uniquely identify by it's name (primary key) and imagine someday you decide to update your departments name - (For example you want to update your department name from "Science" To "Science and Research") – Mehdi Dec 02 '17 at 09:14
  • f.e. for resolving duplicated ids in some unifying data sources. – Oleksii Kyslytsyn Aug 18 '18 at 19:05
  • Another where this is required, the entities PK is shared outside the system, code uses this PK in multiple systems, a new requirement arrives that demands that the entity id be anything different (external system is idempotent so new reqirement to retry in some situation requqires id must be anything different), this requirement comes from outside system using Hibernate, changing existing data is not possible because its a running system only changing newly generated data is going to work. – teknopaul Aug 30 '22 at 10:41
4

My friend, you are right. There is no way (or I don't know a way) to change primary key without using HQL statements. It's not normal, but it's true.

3

This is not a solution, as I believe that has been answered above.

However, I wanted to offer what I considered to be a valid scenario for wanting to do this. (Comments welcome)

1) Table USER_ELECTRONICS has a many-to-many with Table ELECTRONICS_DEF

2) This many-to-many is an ordered list. Each USER_ELECTRONIC_ID has a prioritized list of ELECTRONICS_DEF_IDs. For example, I have an IPhone, an IPad, Cell Phone, and I rank them in order of how much I like them. To accomplish this, I have USER_ELEC_PRIORITY_MAP table, that looks like this -

USER_ELEC_ID ELEC_DEF_ID PRIORITY

1 IPAD 1
1 IPHONE 2
1 CELL 3

USER_ELEC_ID, and ELEC_DEF_ID are PFKs. PRIORITY is Composite FK.

3) When I want to reoder the table in terms of my preferences (My new IPad2 is top on the list, and iPad moves to #4), I need to update part of the Composite FK (PRIORITY).

Erin K
  • 39
  • 1