Create index where column is compared against multiple values-CodePudding

In my application, organizations can configure custom user groups. There are also a few system-level user groups (e.g. Admin) every organization has by default. These are distinguished from custom groups by the fact that user_group.orgId = null rather than pointing to a specific organization.

I currently have a unique index on (user_group.orgId, user_group.name) to prevent users from creating two groups with the same name. But I also want to prevent them from naming their groups the same as a system group. Is this possible in MySQL? I believe we're using 5.7.

CodePudding user response：

If orgId is NULL, you have no way to block people from creating their own Admin group. A foreign key that allows NULLs allows any number of rows to have NULL, and inserting the same value in the second column is not a duplicate, because NULL is not a duplicate of NULL in SQL.

mysql> create table mytable (orgId int, name varchar(10) not null, unique key (orgId, name));

mysql> insert into mytable values (null, 'mygroup'), (42, 'mygroup');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
 ------- --------- 
| orgId | name    |
 ------- --------- 
|  NULL | mygroup |
|    42 | mygroup |
 ------- --------- 
2 rows in set (0.00 sec)

The simplest solution would be to make orgId NOT NULL, and when you create each organization, you must add the special group "Admin" to that org. Therefore the users can't create a group named "Admin" in their org, because it would conflict with the one you already created for their org.