Error:
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes. (Connection: mysql, SQL: alter table `permissions` add unique `permissions_name_guard_name_unique`(`name`, `guard_name`))
It's normal to run into SQL errors when you're working with databases. SQLSTATE[42000]: is one of those mistakes that can confuse developers. 1071: The specified key was too long; the maximum key length is 1000 bytes. This error happens when you try to make or change a table with an index that is too big. To keep your database working well and safe, you need to know what this error means and how to fix it. This is a short guide on what causes this error and how to fix it.
Understanding the Error
The key you gave was too long; the longest key can be 1000 bytes. When trying to create an index in a MySQL or MariaDB database, this error usually happens when the index is longer than the storage engine can handle. This happens most often with string columns (VARCHAR, CHAR, TEXT) that are set to a length that, when added to the maximum byte length of the character set, goes over the limit. For example, UTF-8 characters can take up to 3 bytes each, and UTF-8mb4 characters can take up to 4 bytes each.
Causes of the Error
Since version 5.4, Laravel has used UTF-8 MB4 encoding as its default. This is a good thing because it supports emojis and international characters that regular UTF-8 doesn't. UTF8 uses 3 bytes per character, but UTF8MB4 uses up to 4 bytes. When making indexes, this extra storage space requirement adds up quickly.
Laravel's Default Column Sizes: If you use $table->string('column_name') in a migration without setting a length, Laravel will set it to 255 characters by default. A single column in an index could use up to 1020 bytes (255 × 4) when you combine this with UTF-8 MB4 encoding. If you're making a composite index on two of these columns, you're looking at 2040 bytes, which is way more than the 1000-byte limit that causes the error.
Database Engine Settings: The 1000-byte limit comes from MySQL's InnoDB engine settings, especially when the innodb_large_prefix option isn't turned on or when using older versions of MySQL (before 5.7.7). Different versions and types of database engines have different limits, and sometimes the default settings aren't the best for modern web apps.
Composite Indexes: This error often happens when you try to make unique constraints on more than one column. Even if each column is fine on its own, the total length of all of them in an index might go over the limit. This happens a lot with packages that make tables with more than one string column that needs its own index.
In Laravel 10, go to the location config/database.php
2) Change the Column Sizes:
Check the columns that are being indexed and make them smaller. If you say a column is VARCHAR(255) but it usually has shorter strings, you might want to make it smaller.
Why 191? It is designed to work with UTF-8 MB4 encoding and stay within the limits of most databases: The number of bytes is 764, which is safely below the 1000-byte limit. You will need to run php artisan migrate:fresh again after making this change.
Keep in mind that this error isn't because of bad coding; it's because the system isn't compatible. You can quickly get past this problem and get back to building the main features of your app if you know why it happens and what you can do about it. The main point is that the defaults in Laravel are modern and work well in many countries, but they may need to be changed a little bit to work perfectly with your database.
