[FIXED] how to make a varchar nullable and unique together in mysql (laravel)

Issue

Can i make a MySQL column nullable and unique together. I have a table that store users Email_id if user wants to provide else it will be (null). I read in some other questions that i can make a unique field with default NULL. but i get this error when creating table

#1067 - Invalid default value for 'email' (i make it only for test purpose)

the main table is generated with larave schema builder class

$table->text('email')->nullable()->unique(); (no default added)

in DB

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+ 
| email_id     | varchar(200)     | YES  | UNI | NULL    |                |

now error is

Duplicate entry '' for key 'users_email_id_unique' (when inserting email with empty string) 

Now question is how to handle a varchar field which is unique and nullable together.

Solution

I know this is an old question, this is for others who have the same issue.

You are trying to add an empty string. You made column nullable and unique. That means you are trying something you shouldn't. You can send multiple null values but not mutiple empty string. MySQL consider empty as a value.

or you could write a mutator in your modal

public function setEmailAttribute($value) {
    if ( empty($value) ) { // will check for empty string
    $this->attributes['email'] = NULL;
    } else {
        $this->attributes['email'] = $value;
    }
}

Answered By - Dasun

Answer Checked By - Robin (PHPFixing Admin)