Unique constraints with Spring Data not applied in MySQL database

January 23, 2024

I have this entity defined:

@Entity
@Table(name = "t_transit_aspect_western_es",
        uniqueConstraints =
        @UniqueConstraint(columnNames = {
                "transitPlanet",
                "transitSign",
                "transitHouse",

                "natalPlanet",
                "natalSign",
                "natalHouse",

                "type",
                "isRetrograde",
                "lang"}))
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
@Slf4j
public class TransitAspectWesternEs implements Serializable {

I was able to insert two records with the same values and natal_house=null without any exception from MySQL.

>Solution :

This is how MySQL generally works with NULL and unique constraints. They consider NULL as an absence of value and that NULL is not equal to NULL. That is why several NULL values are not considered as duplicates.
As a solution, you probably should try to avoid unique constraints on the nullable fields.
Here are some links where the problem is explained in details and several solutions are suggested:

Does MySQL ignore null values on unique constraints?

https://medium.com/@aleksandrasays/dealing-with-mysql-nulls-and-unique-constraint-d260f6b40e60

https://dba-presents.com/databases/mysql/274-mysql-unique-index-with-null-values