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
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
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
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.