When trying to set column to Unique, error is generated speaking 767 bytes [duplicate]

Question

When trying to set column to Unique, error is generated speaking 767 bytes [duplicate]

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

When I try to run a migration in 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 unique    usuarios_email_unique ( email ))

[PDOException] SQLSTATE [HY000]: General error: 1709 Index column   size too large. The maximum column size is 767 bytes.

The sql that is running 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 might be causing this?

mysql laravel mariadb xampp

asked by anonymous 08.12.2017 / 18:33

2 answers

Place If no Where (SQL) Each property has several images and a cover image

score 1 · Answer 1

Summarizing columns of type string of Laravel comes with default 255 which causes the size to exceed the maximum size for the index, go to its migration and change the lenght of the field with key 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 method boot() the following statement for your string field to get default and not need to declare in all migration the size of the string field:

Schema::defaultStringLength(191) ;

score 1 · Answer 2

I solved this problem by changing charset and collation in database 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 AppServiceProvider::boot .

Schema::defaultStringLength(191);

Note : I do not think it's a good idea to use the second option, I preferred to change charset and collation .