Laravel 5 migration identifier name too long

Question

I am trying to run the following migration:

public function up()
{
    Schema::create('lifestyle_questions', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('question');
        $table->timestamps();
    });

    Schema::create('lifestyle_question_answers', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('lifestyle_question_id')->unsigned();
        $table->foreign('lifestyle_question_id')->references('id')->on('lifestyle_questions');
        $table->string('answer');
        $table->timestamps();
    });

    Schema::create('user_lifestyle_question_answers', function(Blueprint $table)
    {
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->integer('lifestyle_question_answer_id')->unsigned();
        $table->foreign('lifestyle_question_answer_id')->references('id')->on('lifestyle_question_answers');
    });
}

But I get the following error:

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'user_lifestyle_question_answers_lifestyle_question_answer_id_foreign' is too long (SQL: alter table `user_lifestyle_question_answers` add constraint user_lifestyle_question_answers_lifestyle_question_answer_id_foreign foreign key (`lifestyle_question_answer_id`) references `lifestyle_question_answers` (`id`))

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

score 158 · Accepted Answer · edited Nov 21 '17 at 14:47

158

You can pass a custom index name as the second parameter into the foreign() method. Or just use shorter table/column names.

So you want to do something like:

public function up()
{
    Schema::create('lifestyle_questions', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('question');
        $table->timestamps();
    });

    Schema::create('lifestyle_question_answers', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('lifestyle_question_id')->unsigned();
        $table->foreign('lifestyle_question_id', 'lq_id_foreign')->references('id')->on('lifestyle_questions');
        $table->string('answer');
        $table->timestamps();
    });

    Schema::create('user_lifestyle_question_answers', function(Blueprint $table)
    {
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->integer('lifestyle_question_answer_id')->unsigned();
        $table->foreign('lifestyle_question_answer_id', 'lqa_id_foreign')->references('id')->on('lifestyle_question_answers');
    });
}

edited Nov 21 '17 at 14:47

Chuck Le Butt

47,570
62
203
289

answered May 11 '15 at 14:30

Pawel Bieszczad

12,925
3
37
40

11

Just for information: MySQL identifiers should always be less than 64 characters according to http://dev.mysql.com/doc/refman/5.5/en/identifiers.html – Pᴇʜ May 11 '15 at 14:37
10

Unfortunally, this solution does not work with the `primary()` mehtod when generating composite keys: `$table->primary(['id1', 'id'], 'custom_index');` How to solve this issue? – Dong3000 May 08 '18 at 13:04
3

For those having this problem generating composite keys as @Dong3000, see this: https://stackoverflow.com/a/28626907/3368784 – Giovanne May 16 '19 at 18:40
31

For people using `foreignId` you can use it like so: `$table->foreignId('really_long_foreign_key_id')->constrained()->index('short_id_foreign');` – Fredrik Jungstedt Jun 12 '20 at 13:11

score 1 · Answer 2 · answered Jan 31 '23 at 13:40

If you trying to add a primary key with many columns as a composite key. The best option I have found is to just use a raw query in your migration.

DB::statement('alter table table_name add primary key (column_1, column_2, column_3, column_4, column_5, column_6, column_7)');

Laravel 5 migration identifier name too long

2 Answers2

Linked

Related