MySQL unique index only if column has specific value

Question

I have a table where I use a boolean column deleted to signify whether a specific record has been 'deleted' by the user (meaning, it appears in the trash list instead of the normal list). These items all have slugs which should be unique.

However, if the user wants to add an item with a slug that is the same as the slug of a deleted record, it should be allowed. I tried doing this with a UNIQUE index on deleted and slug, however, this gives a problem if this item would get deleted: there should then be two items in the trash list with the same slug, which would give a constraint violation.

So actually I need a constraint which is only applied for the records with deleted=0

I've seen the question Custom unique column constraint, only enforced if one column has a specific value, however, it only applies to PostgreSQL and I'm on MySQL. Is there a way in MySQL to do this, or a nice workaround?

I thought of making deleted an integer instead of a boolean, where 0 would signify not deleted and any other number deleted. Then deleted items with the same slugs could have a different value in the deleted column.

However, this would mean the interfacing software (written in PHP) would have to check what it could put in the deleted column if a constraint violation occurs, and of course I'd prefer a pure MySQL solution. If I'm going to write this in the PHP code anyway, I'd rather change the slug value, for example, to prepend the id of the record to the slug when an item has been deleted - or, create a second table for the deleted records, where no such constraint exists.

Anyway, is there a pure MySQL way to do this?

---

^{Note: of course, such a scheme will have difficulties when the user requests to restore a previously deleted item when its slug is already assigned to a new record. This will have to be done in the interfacing code in any case.}

Answer 1

You don't have to check what value you could use in "deleted" if you have a way of generating unique integers, and MySQL does... the UUID_SHORT() function generates a unique and monotonic BIGINT UNSIGNED value every time it's called.

The function is good for 16.7M new values per second, every second, and will never return a duplicate (or smaller than previous) value as long as the @@SERVER_ID remains the same and you don't shut down the server, set the system clock backward in time, and restart the server... and even if you do, the odds of duplicate values are still very small, but no longer truly impossible.

You could also make "deleted" be a DATETIME(6) or TIMESTAMP(6) on MySQL 5.6, using '0000-00-00 00:00:00.000000' to mean "not deleted" and NOW() to mean deleted.

Try as I might, I have not been able to get MySQL to produce a NOW() collision down to the microsecond.

Answer 2

I had a similar question, and have a way to make it work, so if you don't mind the kludge, here it is:

Since MySQL allows multiple NULL values on a unique index, you can use NULL to indicate "deleted" and non-NULL value (42?) to indicated "not deleted".

You would then create a composite unique index on [slug, deleted].

The table would look like this:

slug   deleted
abcd   42    # This record is marked as not-deleted
abcd   NULL  # This record is marked as deleted
abcd   NULL  # This record is marked as deleted
xyz    42
xyz    NULL

If using non-NULL to signify "not deleted" seems a bit iffy, so if you can, you could reverse the values and change the column name to "available" or something? :

slug   available
abcd   42    # This record is marked as available
abcd   NULL  # This record is marked as not-available  
abcd   NULL  # This record is marked as not-available
xyz    42
xyz    NULL

Hope that helps