UNIQUE contraint for more then One MySQL columns with Null Values

Question

In my problem, i have two columns in my 'Person' database. One column stores the group the person belongs to and a column which stores that he is the admin of the group. Once group can have only one admin.

My thought is that i should put a unique contraint on both of the columns. But the problem is that for the other users which are not admins they unique contraint gets broken.

What should be the proper solution of he problem.

Example Database

----------------------------------
Person_id | Group_Id  |  Is_admin
----------------------------------
1         | 9         | null
2         | 9         | null
3         | 9         | null
4         | 9         | 1
5         | 9         | null
6         | 4         | null
7         | 4         | null
8         | 4         | null
9         | 4         | 1

Now if I apply unique contraint on Group_Id and Is_admin they are unique but not in the case when the person is not an admin.

Answer 1

It works OK for me...

DROP TABLE IF EXISTS person_group;

CREATE TABLE person_group
(Person_id INT NOT NULL PRIMARY KEY
,Group_Id  INT NOT NULL
,Is_admin TINYINT NULL
,UNIQUE (group_id,is_admin)
);

INSERT INTO person_group VALUES
(1         , 9         , null),
(2         , 9         , null),
(3         , 9         , null),
(4         , 9         , 1),
(5         , 9         , null),
(6         , 4         , null),
(7         , 4         , null),
(8         , 4         , null),
(9         , 4         , 1);

SELECT * FROM person_group;
+-----------+----------+----------+
| Person_id | Group_Id | Is_admin |
+-----------+----------+----------+
|         6 |        4 |     NULL |
|         7 |        4 |     NULL |
|         8 |        4 |     NULL |
|         9 |        4 |        1 |
|         1 |        9 |     NULL |
|         2 |        9 |     NULL |
|         3 |        9 |     NULL |
|         5 |        9 |     NULL |
|         4 |        9 |        1 |
+-----------+----------+----------+

INSERT INTO person_group VALUES (10, 9, null);
Query OK, 1 row affected (0.00 sec)

INSERT INTO person_group VALUES (11, 9, null);
Query OK, 1 row affected (0.00 sec)

Answer 2

Consider creating a new table to contain information about your groups, where group_id is the primary index and a column like admin_id that refers back to your person table as a foreign key. That way you eliminate the is_admin column completely and have a more relational design.