Unique index or primary key violation in H2 create-drop mode

Viewed 1136

I have 3 tables as course, paper & course_paper in a H2 database with create-drop option configured in application.properties. whenever I try to insert data to tables by data.sql I am getting Unique index or primary key violation. Below are my entity class with relationship annotation & data.sql. Please provide some solution or guide me to resolve this issue.

1 : BaseEntity

@MappedSuperclass
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class BaseEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
}

2 : CourseEntity

@Entity
@Table(
        name = "course",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "id")
        }
)

@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
public class CourseEntity extends BaseEntity {

    @Column(name = "stream")
    private String stream;

    @Column(name = "name")
    private String name;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinTable(name = "course_paper", joinColumns = {@JoinColumn(name = "course_id", referencedColumnName = "id")}, inverseJoinColumns = {@JoinColumn(name = "paper_id", referencedColumnName = "id")})
    private Set<PaperEntity> paperEntities;

    public CourseEntity(Course course) {
        this.setId(course.getId());
        this.setName(course.getName());
        this.setStream(course.getStream());
    }
}

3 : PaperEntity

@Entity
@Table(name = "paper",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "id")
        })
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public class PaperEntity extends BaseEntity {

    @Column(name = "name")
    private String name;
    @Column(name = "symbolic_name")
    private String symbolicName;
 
    public PaperEntity(Paper paper){
        this.setId(paper.getId());
        this.setName(paper.getName());
        this.setSymbolicName(paper.getSymbolicName());
    }
}

4 : data.sql

INSERT INTO `course` VALUES (1,'ARTS','+2 1St Year'),(2,'ARTS','+2 2nd Year'),(3,'ARTS','+3 1St Year'),(4,'ARTS','+3 2nd Year'),(5,'ARTS','+3 3rd Year'),(6,'SCIENCE','+2 1St Year'),(7,'SCIENCE','+2 2nd Year'),(8,'SCIENCE','+3 1St Year'),(9,'SCIENCE','+3 2nd Year'),(10,'SCIENCE','+3 3rd Year');
INSERT INTO `paper` VALUES (1,'Pol.Science','Political Science'),(2,'Eco','Economics'),(3,'Math','Mathematics'),(4,'Phy','Physics'),(5,'Chem','Chemestry');
INSERT INTO `course_paper` VALUES(1,1),(1,2),(2,1),(2,2);

5 : Error :

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PUBLIC.UK_HU9K94I7MTNKS4X0Y0207JBGG_INDEX_2 ON PUBLIC.COURSE_PAPER(PAPER_ID) VALUES 1"; SQL statement:
INSERT INTO `course_paper` VALUES(1,1),(1,2),(2,1),(2,2) 
1 Answers

You have a @OneToMany relationship from CourseEntity to PaperEntity. This means each paper may only be referenced by at most one course.

But in the error message you can see that the same PAPER_ID is combined with two courses. This then triggers the error based on the constraint generated by your JPA implementation based on your entity annotations.

To fix this remove either tuples from the insert statement so that each paper gets referenced at most once or change the one-to-many relationship to a many-to-many relationship.

Related