When trying to define column as Unique, error is generated by talking 767 bytes

Question

When I try to run a Migration on Laravel, the following error is generated:

[Illuminate Database Queryexception] SQLSTATE[HY000]: General error: 1709 Index column size Too large. The Maximum column size is 767 bytes. (SQ L: alter table usuarios add-on usuarios_email_unique(email))

[Pdoexception] SQLSTATE[HY000]: General error: 1709 Index column size Too large. The Maximum column size is 767 bytes.

The sql being executed is this:

create table `usuarios` ( 
  `id` int unsigned not null auto_increment primary key, 
  `nome` varchar(255) not null, 
  `email` varchar(255) not null, 
  `password` varchar(255) not null, 
  `nivel_id` int unsigned not null, 
  `empresa_id` int unsigned not null, 
  `departamento_id` int unsigned null, 
  `cargo_id` int unsigned null, 
  `status` tinyint(1) not null default '1', 
  `created_at` timestamp null, 
  `updated_at` timestamp null) 
default character set utf8mb4 collate utf8mb4_unicode_ci

alter table `usuarios` add unique `usuarios_email_unique`(`email`)

alter table `usuarios` add index `usuarios_password_index`(`password`)   

What could be generating this?

Answer 1

I solved this problem by changing the charset and the collation in the dataset settings, in config/database.php:

//'charset' => 'utf8mb4',
//'collation' => 'utf8mb4_unicode_ci',
'charset'   => 'utf8',
'collation' => 'utf8_unicode_ci',

Another solution, already presented in the other question, but can be done globally rather than individually, is to set the maximum string size to 191. This should be done in the AppServiceProvider::boot.

Schema::defaultStringLength(191);

Note: I don’t think it’s a good idea to use the second option, charset and the collation.

Answer 2

Summarizing the columns of the type string of Laravel comes with default 255 which causes the size to exceed the maximum for the Internet, go to your migration and change the lenght key field unique to 191 or to the size you prefer, for example:

$table->string('email', ['length' => 191])->unique();

You can add in your file AppServiceProvider.php within the method boot() the following instruction for your field string stick around default and do not need to declare at all migration the size of the string field:

Schema::defaultStringLength(191);