How do I not allow a specific value duplicating in the database

Question

so I wanted to create column let's say master_user, so this master_user column has bit values, 0 and 1. What I wanted to achieve is to make it so that the column master_user has only one '1' value as there are only one master_user. You can have multiple '0' values but not '1'.

In SQL Server, you can do as so:

CREATE UNIQUE NONCLUSTERED INDEX [IX_master_user] ON [user]
(
    [master_user] ASC
)
WHERE ([master_user]=(1));

How do I do the same for MySQL ?

Thank you.

UPDATE : MySQL does not support Filtered Index and Conditional Index

I'd suggest reading the mysql [if operator](https://dev.mysql.com/doc/refman/8.0/en/if.html) documentation and checkout [here](https://stackoverflow.com/questions/6854996/mysql-insert-if-custom-if-statements) for an example. — Muhammad Hamza, Feb 13 '19 at 06:13
Thank you, but I wanted to create a column pre-defined with the attribute of not allowing duplicated bit '1'. — Nik Mohamad Lokman, Feb 13 '19 at 06:16

score 0 · Answer 1 · answered Feb 13 '19 at 06:34

The easiest approach would probably be to use 1 to designate a master user and null to designate a non-master. Since nulls aren't values (they are the lack thereof), they don't participate in unique indexes, and having a simple unique index on the master_user column would do the trick.

If you absolutely must keep the 0s, you could add a generated column that substitutes 0 for null and make it unique, to the same effect:

CREATE TABLE users (
  username VARCHAR(10) PRIMARY KEY,
  master_user BIT NOT NULL,
  master_user_nullable BIT AS (CASE master_user WHEN 1 THEN 1 END) STORED UNIQUE
);

How do I not allow a specific value duplicating in the database

1 Answers1