MySQL - Make an existing field unique without deleting empty entries

50 views Asked by At
ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

This will make a filed a unique index but the command deletes all rows with an empty value, is there any way I can avoid that happening. The field may have NULL values if that helps.

1

There are 1 answers

0
Michael - sqlbot On

Set the "empty" (?) values to NULL and don't use IGNORE. The purpose of ALTER IGNORE is to remove duplicate values. Multiple NULL are not duplicates of each other, since the expression NULL = NULL is not true (and is also not false).