CODEWITHSUNDEEP
mysqlsqldatabase
Meqenaneri Vacharq
Meqenaneri Vacharq

Reputation: 99

create unique index multiple columns with one is columns value is zero

I have a table users with following columns and attributes

users (
  id int(11), 
  ssn varchar(100), 
  foreigner tinyint(1) default 0
)

foreigner values is 0 and 1 default 0 how I can create unique index on column ssn, when checked foreigner value is 0.

id     ssn    foreigner
1       55         0     true 
2       60         1     true
3       60         1     true
----------------------------------
4       55         0     false
----------------------------------
5       77         0     true

Upvotes: 0

Views: 379

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can only do this without a trigger in MySQL using the most recent versions of MySQL. 

create table users (
    id int(11),
    ssn varchar(100),
    foreigner tinyint(1) default 0,
    foreigner_ssn tinyint generated always as (case when foreigner = 0 then ssn end) store unique
);

This creates a new generated column. MySQL unique indexes to repeat NULL values, so the condition is really only on foreigner = 0.

You could possibly simplify this, just by having ssn be NULL when foreigner = 0 and declaring that to be unique.

Upvotes: 1

Related Questions