Now I have users table below and want email column unique if id has prefix
|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.
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.
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.
Answered By – Akina
Answer Checked By – Jay B. (BugsFixing Admin)