11

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.

Natwar Singh
  • 2,197
  • 4
  • 26
  • 42

3 Answers3

15

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;
    }
}
Dasun
  • 3,244
  • 1
  • 29
  • 40
1

We just make a mistake when have to insert null. In case of null we have to declare null like this:

if(empty($request->email_id)){
    $user->email_id = NULL;
}

Don't use "NULL", this will be considered as string.

Shahadat Sunny
  • 11
  • 1
0

You can use $table->string('mobile')->nullable()->unique(); inside Laravel migration

Joyanta sarkar
  • 53
  • 1
  • 8
  • *"Any answer that gets the asker going in the right direction is helpful, but do try to mention any limitations, assumptions or simplifications in your answer. Brevity is acceptable, but fuller explanations are better."* - check [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). – Kuro Neko May 19 '22 at 08:28
  • If there's already data in the database, it also needs ->default(null) at the end, otherwise default value will be an empty string. When it's not null, string is checked for uniqueness and uniqueness check fails if multiple values are empty strings. – soggypants May 24 '23 at 19:36