create unique index using where condition-CodePudding

Now I have users table below and want email column unique if id has prefix auth0|.

column
id	text
email	text not null

So I tried this CREATE UNIQUE INDEX statement.

CREATE UNIQUE INDEX auth0_users_email_idx ON users (email) WHERE id LIKE 'auth0|%';

This worked as I expected in postgres14, but not in mysql8 with error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id LIKE 'auth0|%''

I want to do the same thing in mysql but, I couldn't find correspondence. Anyone knows how to solve this issue? Thanks in advance.

CodePudding user response：

CREATE UNIQUE INDEX auth0_users_email_idx 
ON users ((CASE WHEN id NOT LIKE 'auth0|%' THEN email END));

If the prefix matches then CASE returns NULL which is not checked for uniqueness. Otherwise it returns email.

But MySQL does not allow to index TEXT column. Either alter column datatype or CAST the value to CHAR in the index expression.

DEMO fiddle

PS. Pay attention to double brackets - they're compulsory. Outer brackets wraps the index expression, inner ones encloses an expression and convers is to scalar value.