0

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?

1

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.

1
  • I should've thought of that. It's that simple. Thanks for the rubber duck.
    – Julian
    Mar 10 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.