Mysql unique value or empty string

Question

I have an email mailing list table. There is an activation key for each user that signs up. The activation key is initially created upon signup. Each key is unique.

Once the user confirms registration this is an emptry string in the database.

Is there a way of me creating a unique contraint if there is null in the field.

In other words all value if not null or emptry string that are unique are allowed.

I set to unique and allow null but this does not work. I cant enter null and unique values, i.e. null multiple times when set to null unique

Answer 1

No, you won't be able to create a unique constraint like that because a unique constraint is just that, unique, and multiple rows with NULL or an empty string would be duplicated rows. However, I would recommend you not empty out this column and rather use a boolean field that indicates registration was confirmed - it's a lot more straight forward and then you can use a unique constraint on that field.

Answer 2

in mysql

column or unique index only check constraint when all values filled without null values