How to name MySQL foreign keys for long table names in Laravel migrations

,

After running a freshly created Laravel DB migration I got error:

1
SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'multistep_problem_part_attempts_multistep_problem_part_id_foreign' is too long

It turns out MySQL has a limit of 64 characters for identifier names.

Laravel generates identifier name based on table name, column names and index type and doesn’t care about the limit. So you have to set the name manually.

After figuring this out I replaced:

1
$table->foreignId('multistep_problem_part_id')->constrained('multistep_problem_parts');

with:

1
2
3
4
5
$table->unsignedBigInteger('multistep_problem_part_id');
$table
	->foreign('multistep_problem_part_id', 'multistep_problem_part_id_foreign')
	->references('id')
	->on('multistep_problem_parts');

That should do the trick, right? Well, I got another error:

1
SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'multistep_problem_part_id_foreign'

MySQL documentation says:

The CONSTRAINT symbol value, if defined, must be unique in the database.

This identifier name was already used in a migration for another table, while solving the same limit-related problem. Both migrations had the same mistake of using too generic identifier name.

The final solution was to make the identifier globally unique by adding more information while keeping it under the limit of 64 characters:

1
2
3
4
5
$table->unsignedBigInteger('multistep_problem_part_id');
$table
	->foreign('multistep_problem_part_id', 'multistep_problem_part_attempts_multistep_problem_part_id_fk')
	->references('id')
	->on('multistep_problem_parts');

The exact format is up to your imagination but it would be wise to keep it consistent across all tables.