SQL Constraints: Unique value in a non-unique column

Question

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

Gordon Linoff · Accepted Answer · 2016-11-18 10:58:35Z

2

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

answered Nov 18, 2016 at 10:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Constraints: Unique value in a non-unique column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related