CODEWITHSUNDEEP
mysqlsql
MultiDev
MultiDev

Reputation: 10649

SQL: Unique index for two columns where value of one is NOT DEFAULT

I have the following table structure:

CREATE TABLE `users` (
  `id` varchar(36) NOT NULL,
  `group_id` varchar(36) NOT NULL,
  `group_owner` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`));

With group_owner acting as a boolean, I am trying to make a unique index to where any given group ID can only have one row where group_owner = 1.

Something like:

UNIQUE KEY (`group_id`,`group_owner`) ... WHERE group_owner NOT DEFAULT

How would I go about doing this?

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You have the wrong structure for your data. What you need is a groups table. And that should have the foreign key reference:

CREATE TABLE `users` (
  user_id varchar(36) PRIMARY KEY,
  group_id varchar(36) NOT NULL
);

create table groups (
    group_id int auto_increment primary key,
    group_name varchar(36) not null,
    owner_user_id varchar(36),
    constraint fk_groups_owner foreign key (owner_user_id) references users (user_id)
);

alter table users add constraint fk_users_groups
    foreign key (group_id) references groups(group_id);

Voila! Only one owner per group.

Notes:

  • I made group_id an integer. I find that auto-incremented values are better for primary keys. I would recommend doing the same for users as well.
  • This allows a user to be in only one group.
  • In practice, you will insert a group with a NULL owner. Then insert the user with the appropriate group, and then set the owner to the user.

Actually, the above data model runs the risk that a user could be the owner of a group s/he is not a member of. That is fixed with a slight tweak:

CREATE TABLE `users` (
  user_id varchar(36) PRIMARY KEY,
  group_id varchar(36) NOT NULL,
  UNIQUE (group_id, user_id)
);

create table groups (
    group_id int auto_increment primary key,
    group_name varchar(36) not null,
    owner_user_id varchar(36),
    constraint fk_groups_owner foreign key (group_id, owner_user_id) references users (group_id, user_id)
);

alter table users add constraint fk_users_groups
    foreign key (group_id) references groups(group_id);

Upvotes: 1

Related Questions