Mysql – The possibility to define a composite unique constraint for specific cases

database-designMySQLunique-constraint

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?

Best Answer

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.

Related Question