I have a MySQL table, that has a unique index constraint over multiple (2) columns: "username", and "provider". The index works fine and does not allow inserting rows with duplicated usernane+provider combination.
But I do want to allow insertion of the same usernane+provider combination in case another column, "active", has the value false. I do not have a problem with multiple duplicate rows all marked as active FALSE. MySQL, unlike Microsoft SQL Server, does not support conditional/filterable indexing.
While I could not find a more appropriate solution, I thought to use a trigger (after insertion to the table) to check whether the value that was just inserted is duplicated, considering the "active" value.
Can I get the trigger to fail the INSERT statement? Is there a better solution for this case?