how to swap records having unique constraint in hibernate

user_1169474 0
Viewed 1982

I have 2 tables user and userinfo. userinfo table contains user_id(id of user table) column which has UNIQUE constraint. now i have 2users(primaryUser and secondaryUser) which has records in user and userInfo tables.

The primaryInfo object contains primaryUserId and secondaryInfo object contains secondaryUserId

I want to swap the userinfo data of primaryUser to secondaryUser and viceversa. I am doing like this

primaryInfo.setUserId(secondaryUser.getId());                       
secondaryInfo.setUserId(primaryUser.getId());

session.update(primaryInfo);
session.update(secondaryInfo);

but when commiting the transaction it is giving error like ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper:147 ERROR: duplicate key value violates unique constraint "user_infos_unique_user" Detail: Key (ui_user_id)=(52560087) already exists.

can you please tell how to do this.. Thanks

postgresql hibernate java
4 Answers

You can use the DEFERRABLE and INITIALLY DEFERRED properties on the constraint and update both records in a single transaction. DEFERRED means the constraint will not be evaluated until the transaction is commited -- at which time it should be valid again.

However: I have not figured out how to use Hibernate annotations to specify the DEFERRED properties, so you will have to use LiquiBase to maintain the database schema (not a bad idea anyway.) (Or use "raw" SQL which is not so good an idea.)

See this question for more about the annotations (alas I cannot use LiquiBase on the project I ask about there.)

user_3918060

For Oracle database you can create next unique constraint with special attributes 'DEFERRABLE INITIALLY DEFERRED':

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (table_field) DEFERRABLE INITIALLY DEFERRED
user_59258980
Related