The possibility to define a composite unique constraint for specific cases I have two tables: user and address.
User table:




id
email
name




1
henk@example.com
Henk


2
john@example.com
John




Address table:




id
user_id
street
is_default




1
1
Super cool street
1


2
1
Beautiful avenue
0


3
1
Some street
0


4
2
Damn cool street
0


5
2
Cool street
1


6
2
My street
0




The problem
Image someone setting two default addresses for a user; it would be non-sensical from the Business logic perspective.
The question
I want to create a unique constraint composed by user_id and is_default but only for a specific case, which is that the constraint must be enforced when is_default is 1.
Is this possible with mysql?
Add a foreign key column in the user table that references the primary key of the address table. Call it default_address_id. This enforces uniqueness because it can have only one value at a time for a given user.
Then drop the is_default column in your address table because it risks disagreeing with the foreign key in the user table.