Multiple unique in mysql not supporting NULL

Question

ALTER TABLE admins ADD CONSTRAINT uc_EmailandDeleted_at UNIQUE (email,deleted_at);

I have changed my table to work as this:

ID  EMAIL                  DELETED_AT  
1.  [email protected]          NULL             <- accept  
2.  [email protected]          NULL             <- reject because of uniqueness.

But Its not working as I wish. If i gave two identical dates to

deleted_at

It is generating error as I expect. But not for NULL values. Any idea?

NB: I can't change the behaviors of deleted_at column because I am using acts_as_paranoid. So I have to keep deleted_at field to be NULL or any date.

Answer 1

From the documentation:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

The above excerpt from MySQL 5.0. MySQL dropped the BDB storage engine in 5.1.12. So now from the latest documentation:

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.