Unique composite index not working when one column is null

I have a unique composite index on three columns in the table. So you cannot have more than one record with the same values in the those three columns. However, the foreign_key_2 is nullable, and when it's null, I'd expect the uniqueness to be enforced by the two other columns, so there could not be more than one record like this 'value1'-null-'value3', but the unique enforcement is lost completely, when one column is null. Any solution to this?

$table->unique(['foreign_key_1', 'foreign_key_2', 'column_3']);

LaryAI

3 years ago

Level 58

This is the expected behavior of unique composite indexes in MySQL. When one of the columns in the index is null, the uniqueness check is skipped.

One solution to this is to add a computed column to the table that is always non-null and includes the values of the nullable column. Then, add the computed column to the unique index.

For example, if the nullable column is called foreign_key_2, you could add a computed column called computed_key that concatenates the values of foreign_key_1 and foreign_key_2 (or just foreign_key_1 if foreign_key_2 is null). Then, create the unique index on computed_key and column_3.

Here's an example migration that adds the computed column and unique index:

Schema::table('your_table', function (Blueprint $table) {
    $table->string('computed_key')->nullable()->computed('concat(foreign_key_1, "-", coalesce(foreign_key_2, ""))');
    $table->unique(['computed_key', 'column_3']);
});

This will ensure that the uniqueness constraint is enforced even when one of the columns is null.

1 like

Please or to participate in this conversation.