Index: Avoid duplicates in table when Status = 'S'

Question

Is there a way of avoid duplicate rows only when status = 'S'? Example:

table showing what I need to do.

I tried to create an index "unique" but when status = 'N' it should allow duplicate, then it did not work correctly.

Any sugestions to avoid duplicates in SQL?

user_5070879 · Answer

Yes, you could use filtered index:

CREATE UNIQUE INDEX UIX_name ON tab(fk_client) WHERE status = 'S';