@oneToMany with @JoinTable have unique constraint in jpa?

Question

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.

Seems like its a many -to-many ... Don't u need to have a role / user and user_role ? — Pras, Jul 18 '16 at 12:16

score 4 · Answer 1 · answered Jul 18 '16 at 12:16

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

score 2 · Answer 2 · answered Jul 18 '16 at 18:05

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.

@oneToMany with @JoinTable have unique constraint in jpa?

2 Answers2