Circumspect Squid Circumspect Squid - 20 days ago 5
MySQL Question

SQL Constraints: Unique value in a non-unique column

I have a table named GroupMembers, which represents a list of users participating in certain communities on a website.

Columns look something like this:

groupId | accountId | role


As you can see, inside I have a CHAR(1) column named 'role', which represents the role of every user in the community. The primary key comprises the first two columns, and the 'role' is neither NOT NULL, nor UNIQUE.

Now, here's the problem: every group must have the account of its creator specified, which I thought I could implement by adding the account to GroupMembers and putting 'C' in its 'role' column.
But there can only be ONE creator, other accounts in the group can't have 'C' as their 'role'.

Is there a constraint that would allow me to check if there is only one row with 'C' in its 'role' column, apart from UNIQUE (which I can't use since there can be any number of every other type of user)?

Answer

Such a constraint exists . . . but not in MySQL. Other database allow you to implement filtered indexes or indexes on computed columns.

Instead, you can implement this logic in the Groups table by having the creator:

alter table groups add column creatorUserId int;
alter table groups add constraint fk_groups_creatorUserId
    foreign key creatorUserId references users(creatorUserId);