CODEWITHSUNDEEP
mysqlsql
Ahmed Rasmi
Ahmed Rasmi

Reputation: 21

CREATE UNIQUE INDEX using where clause

I'm having trouble using a where clause on CREATE UNIQUE INDEX on mysql. I know you cannot just add where clause at the end of CREATE UNIQUE INDEX. Example below. 

CREATE UNIQUE INDEX FAKE_TABLE_INDEX ON TABLE_NAME (COLUMN_NAME) WHERE INACTIVE = 0;

and this query above gives me an error. So is there alternative I can do to fix this query.

Upvotes: 2

Views: 9675

Answers (1)

Barmar
Barmar

Reputation: 780818

MySQL doesn't have filtered indexes. If I understand what they do (from reading the Microsoft docs) I think the closest analogous feature is multi-column indexes:

CREATE INDEX fake_table_index ON table_name (inactive, column_name);

This is more expensive than the filtered index because it indexes all the values of inactive, not just where inactive = 0.

This also doesn't have the unique constraint that the filtered index does. It's only useful for optimizing queries, not enforcing uniqueness. You'll have to do that with a trigger if you need it.

Upvotes: 3

Related Questions