Mysql Unique constraint over multiple columns

Question

I have this table and, as the code shows, I have marked columns "cnpj, product, proposalNumber" as a unique composed constraint:

    @Table(name = "Proposal", uniqueConstraints = {@UniqueConstraint(columnNames = {"cnpj", "product", "proposalNumber"})})
    public class Proposal {
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "id", unique = true, updatable = false, insertable = false)
        @JsonProperty("id")
        private Long id;
    
        @JsonProperty("cnpj")
        @Column(name = "cnpj", nullable = false, length = 14)
        private String cnpj;
    
        @JsonProperty("proposalNumber")
        @Column(name = "proposalNumber", nullable = false)
        private String proposalNumber;
    
        @JsonProperty("product")
        @Column(name = "product", nullable = false, length = 100)
        private String product;
    
        @JsonProperty("price")
        @Column(name = "price", nullable = false)
        private BigDecimal price;
    
        @JsonProperty("dueDate")
        @Column(name = "dueDate", nullable = false)
        private String dueDate;
    
        @JsonProperty("qtyLife")
        @Column(name = "qtyLife", nullable = false)
        private Integer qtyLife;
    
        @JsonIgnore
        @Column(name = "active", nullable = false)
        private Boolean active = true;

        ...

But, checking the DDL or DUMP ain't no unique information...

CREATE TABLE `proposal` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `active` bit(1) NOT NULL,
  `cnpj` varchar(14) NOT NULL,
  `due_date` varchar(255) NOT NULL,
  `price` decimal(19,2) NOT NULL,
  `product` varchar(100) NOT NULL,
  `proposal_number` varchar(255) NOT NULL,
  `qty_life` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

In addition to this, writing @Column(unique = true) on the column does not solve the problem either, it creates the unique constraint in the database but only referencing that single column, not the composition (cnpj, product and proposalNumber).

Any tips?

Why don't you generate the DDL yourself? In production environments that's often the preferred way so that errors in the mapping can't mess up your database. — Thomas, Mar 23 '21 at 14:41
I believe this question may help you: https://stackoverflow.com/questions/2772470/how-to-introduce-multi-column-constraint-with-jpa-annotations — aUser, Mar 23 '21 at 14:47
@Thomas, at this point I am just the guy who must have it done this way, unfortunatelly not the one who gives the rules haha — Léo Eduardo Silva, Mar 23 '21 at 14:53
@Folkem I think that is just what I did, but checking de DDL it does not have the composed unique constraint and if "this does not magically create the unique constraint in the database, you still need a DDL for it to be created" what is the purpose of declaring uniqueConstraints = {@UniqueConstraint ... ? I am confused right now — Léo Eduardo Silva, Mar 23 '21 at 14:54

score 0 · Accepted Answer · answered Mar 26 '21 at 15:44

0

The problem is solved: nothing wrong with my solution, but I had to change my spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect to spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

answered Mar 26 '21 at 15:44

Léo Eduardo Silva

131
11

Mysql Unique constraint over multiple columns

1 Answers1