CODEWITHSUNDEEP
mysqloracle-databasehibernatejpajpa-annotations
Gopi Lal
Gopi Lal

Reputation: 427

@oneToMany with @JoinTable have unique constraint in jpa?

I have a two tables which are USER entity have

@OneToMany
@JoinTable(name="user_roles")
private List<Role> roles;

Role Entity have User

@ManyToOne
private User user;

Desc of table user_role is 

Name                     Null     
USER_RECORD_ID  NOT NULL NUMBER(19) 
ROLE_RECORD_ID NOT NULL NUMBER(19)

NOTE: A user can have multiple roles,and i have already created roles through script,have Id:10001,10002,10003 etc

In user_role role table i am inserting one user 800001 with all the roles so the table looks like 

USER_RECORD_ID  ROLE_RECORD_ID
800001          10001
800001          10002
800001          10003
800002          10001   ///This record will through me unique constraint error

So if i try to give a role to new user where role is predefined it throughs me this error

INSERT INTO USER_ROLE(USER_RECORD_ID,ROLE_RECORD_ID) VALUES(800002,10001)

Error report - SQL Error: ORA-00001: unique constraint (SYSTEM.UK_LPLHY51JOJA1LP4465QK2E0AF) violated 00001. 00000 - "unique constraint (%s.%s) violated" *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.

Upvotes: 3

Views: 4465

Answers (2)

Chris
Chris

Reputation: 21145

You have setup your mappings incorrectly, as it seems you have intended to have a bidirectional relationship that uses a relation table but instead have setup two independent relationships. The first, User.roles uses the relation table, but the otherside 

@ManyToOne
private User user;

is telling JPA to setup a Role-User relation that uses a foreign key in the Role table. This doesn't seem to be the source of your problem, but will cause you other issues and doesn't match what you are asking for- Your role can only reference a single User, yet you are asking for roles to be assigned multiple users. Try:

@ManyTooMany
@JoinTable(name="user_roles")
private List<Role> roles;

.. and in the Role entity:

@ManyTooMany(mappedby"roles")
private List<User> users;

Also make sure you drop the database schema and let JPA recreate the database using these new mappings.

Upvotes: 2

J. Schneider
J. Schneider

Reputation: 1000

I think the error is caused by the use of @ManyToOne/@OneToMany while the relation you have is @ManyToMany. This is so because in the example you give the USER_RECORD_ID with the value 800001 has multiple ROLE_RECORD_ID and the ROLE_RECORD_ID with the value 10001has multiple USER_RECORD_ID.

Therefore try using @ManyToManyinstead, this should fix your problem. Here is a reference in case you need it: https://en.wikibooks.org/wiki/Java_Persistence/ManyToMany

Upvotes: 4

Related Questions