I have a standard User entity, with a name, mobile phone number, and email address. Of course, I don't want any duplication in the email address, but I also don't want it to be required. I'm not sure if a unique constraint lets multiple records be blank. (Wouldn't that violate uniqueness?) Is this even possible? If so, how do I specify this constraint in JPA?
Asked
Active
Viewed 108 times
0
-
1This depends on the database. Some databases allow duplicates with `NULL` values; some only allow one `NULL` value. – Gordon Linoff Jan 05 '21 at 20:56
3 Answers
1
In a word - yes, this is possible. Uniqueness means the column can't have two equal values. nulls in SQL are not values - they are the lack thereof. Since they are not values, they don't affect the uniqueness, and a unique column can have as many nulls as you like.
In JPA, you can specify this with the unique attribute of the @Column annotation:
@Column(unique=true)
String email;
Mureinik
- 297,002
- 52
- 306
- 350
-
`Since they are not values, they don't affect the uniqueness, and a unique column can have as many nulls as you like` => [Counterexample](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6bd76d0895e38e02027202d05693dec8). It depends on RDBMS, but could be easily resolved with filtered unique index – Lukasz Szozda Jan 05 '21 at 20:42
0
Unique constraint won’t allow multiple null valujes but there is a solution for that - https://www.red-gate.com/simple-talk/blogs/allow-nulls-unique-fields/
Josef Veselý
- 102
- 1
- 4
0
Using a unique constraint on the email column will do what you want. This has been answered previously here - Allow null in unique column.
@Column(unique=true)
String email;
Tristan McSwain
- 1
- 1