How to define unique indexes when softDeletes is enabled

Question

Will Laravel 4.1 manage the creation of a unique index(where deleted_at = null) by itself when softDeletes?

Is the approach below correct? Or is it going to mix in already deleted records?

Schema::create('example', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('example')->unsigned()->unique(); //?????
    $table->softDeletes();
});

The database is mysql, but if there's certain solutions for other DB:s, you can provide them as well. However, it should be done within the laravel framework! A uniform solution that works with all dbs that laravel officially supports is appreciated.

Update It seems like this approach does not work, since it just ignores the softDeletes() option.

So proposed solution

Schema::create('exampe', function(Blueprint $table) {
    $table->increments('id');
    $table->integer('example')->unsigned();
    $table->softDeletes();
    $table->unique('example', 'deleted_at');
});

Problem is that there can potentially be two exactly similar timestamps at the deleted_at column.

What I actually need is a where-condition.

$table->unique('example', array('where', 'deleted_at', '=', null));

or

$table->integer('example')->unsigned()->unique()->where('deleted_at', '=', null)

Answer 1

I would recommend making a two-column UNIQUE constraint over the column you want to be unique (example) and a dummy column for instance called is_live. This column is always '1' when the row is not soft-deleted. When you soft-delete a row, set is_live=NULL.

The reason is related to the way "uniqueness" is defined. Unique constraints allow any number of rows that have a NULL value. This is because NULL is not equal to NULL in SQL, so therefore two NULLs count as "not the same".

For multi-column unique keys, if any column is NULL, the whole set of columns in the unique key behaves as if it's not the same as any other row. Therefore you can have any number of rows that have one column of the unique key the same, as long as the other column in the unique key is NULL.

create table example (
  id serial primary key,
  example int unsigned not null,
  is_live tinyint default 1,
  unique key (example, is_live)
);

Demo: http://sqlfiddle.com/#!9/8d1e4d/1

PS: The direct answer to your question, about implementing a condition for indexes in MySQL, is that MySQL doesn't support this. Databases that do support partial indexes include:

• PostgreSQL (https://www.postgresql.org/docs/current/static/indexes-partial.html)
• Microsoft SQL Server (https://msdn.microsoft.com/en-us/library/cc280372.aspx)