Migration index too long? Choose the name yourself!
Migrations are a great way of building database schema, but sometimes it’s harder to deal with more than just columns. One of more interesting things are indexes. In particular, I had a problem of auto-assigning a name to unique index, which appeared to be too long. What to do with it?
A specific example – let’s say you want to set up a unique index on four columns:
|
1 2 3 |
$table->unique(['product_id', 'company_id', 'price', 'delivery_hours']); |
With that you can run into this error:
|
1 2 3 4 5 6 |
SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'prices_history_product_id_company_id_price_delivery_hours_unique' is too long |
The thing is that Laravel is trying to automatically assign a name to the index, and MySQL has a limit here: 64 characters for identifier name.
Solution is to specify your own name for the structure. If you read official laravel documentation, it’s not mentioned, the examples are pretty simple:
But if you dig deeper, then the exact function unique() is defined like this:
|
1 2 3 |
unique(string|array $columns, string $name = null) |
Which means that you can specify your own name for the index.
|
1 2 3 4 |
$table->unique(['product_id', 'company_id', 'price', 'delivery_hours'], 'prices_history_index_unique'); |
Notice: Just make sure it’s not longer than 64 characters and is not among MySQL reserved words.
Notice 2: Same rule applies to other index identifiers – functions primary() or just index() -you can specify the names as well, instead of allowing Laravel to auto-assign the names.
Liked the article? Maybe you need help with your Laravel project?
Check out my Upwork profile and hire me!
Check out my Upwork profile and hire me!
I was searching for a solution to this. Great tip, thanks.
usually i’ve ended up assign each field a key