Home JPA - One to many - duplicate key value error
Reply: 3

JPA - One to many - duplicate key value error

Denis Stephanov
1#
Denis Stephanov Published in 2017-12-05 14:09:01Z

I need implement join table for users which will have 2 columns USER_CONTACTS(user_id, contact_id)

I need store for contacts lists for users. I need to make possible that user with id 1 can have in contacts user with id 5, but user 5 can also have in contacts user with id 1 (but he musn't).

I need to be possible do following inserts:

INSERT INTO USERS_CONTACTS(user_id, contact_id) VALUES(1, 5);
INSERT INTO USERS_CONTACTS(user_id, contact_id) VALUES(5, 1);

Now I got this error:

Unsuccessful: INSERT INTO USERS_CONTACTS(user_id, contact_id) VALUES(2, 3) 2017-12-05 15:00:15.146 ERROR 14268 --- [ main] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: duplicate key value violates unique constraint "uk_j9ggomsdbjte1eqfo5e61vh8a"

Here is my relationship implementation of User in JPA:

@Entity
public class User{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", columnDefinition = "serial")
    private long id;

    @OneToMany
    @JoinTable(name = "USERS_CONTACTS",
            joinColumns = @JoinColumn(name = "USER_ID"),
            inverseJoinColumns = @JoinColumn(name = "CONTACT_ID"))
    private List<User> contacts;

   // ...
}

Can you tell me how to fix it? I am using Postgre 9.5. Thanks.

EDIT

For hibernate I have these properties:

spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = create-drop
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
v.ladynev
2#
v.ladynev Reply to 2017-12-05 14:29:57Z

Try to solve this task yourself.

  1. Enable Hibernate SQL log
  2. Create a database schema with Hibernate on a test database (a tablespace) or using H2 database. You can download this project and play with mappings using unit tests: https://github.com/v-ladynev/hibernate-experimental.
  3. Check generated SQL.

Perhaps, you will notice that contact_id is made unique by uk_j9ggomsdbjte1eqfo5e61vh8a constraint. The reason is that for @OneToMany relation child must has only one parent.

I think you need @ManyToMany relation.

Claus Radloff
3#
Claus Radloff Reply to 2017-12-05 14:16:22Z

There must not be an unique index on that table! Remove that index and it should work.

ThomasEdwin
4#
ThomasEdwin Reply to 2017-12-05 14:16:56Z

I assume that uk_j9ggomsdbjte1eqfo5e61vh8a is a leftover from previous version.

If it were so then just drop the constraint:

alter table USERS_CONTACTS drop constraint uk_j9ggomsdbjte1eqfo5e61vh8a;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.38663 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO