1

I have a problem with UNIQUE CONSTRAINT.

I need to add constraint based on 2 columns, where one of them need to has specific value.

For example let's say that I have a users table and it contains users, of course, and the primary key is just an incremented integer.

And I have a configurations table which has two columns in addition to the configuration columns: user_id (foreign key of the users table) and main which takes 0/1 values.

The logic is as follows: One user can have many configurations, one configuration belongs to one user - as shown in the tables. However, I wanted to restrict it so that one user can only have one main configuration (main = 1) and thought about unique.

I've tried

ALTER TABLE `configurations` ADD UNIQUE `unique_main_user`(`user_id`, `main`);

but that will limit me to one main and one non-main configuration, which is messy.

Is there any possibility to add WHERE statement or any other solution that will allow me to achieve this effect?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
LordF
  • 407
  • 5
  • 18

1 Answers1

5

Since MySQL allows multiple NULLs in a column with a unique constraint you can add a case expression to the constraint to return null for anything other than the main configuration, which will allow duplicates for non-main, but only one main:

CREATE TABLE Configuration 
(
    ConfigurationID  INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    UserID INT NOT NULL,
    Main BIT NOT NULL,
    UNIQUE (UserID, (CASE WHEN Main = 1 THEN 1 END))
 );

Example on db<>fiddle

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • A very interesting approach to the problem. The side effects of the additional column remain problematic. But it's quite good. – LordF Aug 02 '21 at 15:35
  • 2
    I've just realised that the additional column is not required, you can put the case expression directly in the constraint definition (I've updated the answer to show this as it is much cleaner) – GarethD Aug 02 '21 at 15:58
  • 1
    Great. I didn't expect you could use CASE in DDL statement. – LordF Aug 03 '21 at 09:59