MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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))
 );

INSERT Configuration (UserID, Main) VALUES (1, 0);
INSERT Configuration (UserID, Main) VALUES (1, 1);
-- Add second "Non Main" configuration
INSERT Configuration (UserID, Main) VALUES (1, 0);
-- Add second "Main" configuration -- fails with duplicate key
INSERT Configuration (UserID, Main) VALUES (1, 1);
Duplicate entry '1-1' for key 'Configuration.functional_index'

SELECT *
FROM Configuration;


ConfigurationID
UserID
Main


1
1
0

2
1
1

3
1
0