Ad

Laravel Can't Add Foreign Key Even If Assigned As Unsigned

- 1 answer

I have a table named choices and I want the choice_id to be primary key BUT not auto increment because I got a seeder for it.

Here is my migration for choices

     Schema::create('choices', function (Blueprint $table) {
            $table->bigInteger('choice_id')->unsigned();
            $table->bigInteger('question_id')->unsigned();
            $table->string('choice');
            $table->integer('value');
            $table->timestamps();
        });

        Schema::table('choices',function (Blueprint $table){
            $table->foreign('question_id')
                    ->references('question_id')
                    ->on('questions');
        });

Also, here is for the answers

     Schema::create('answers', function (Blueprint $table) {
            $table->bigInteger('user_id')->unsigned();
            $table->bigInteger('question_id')->unsigned();
            $table->bigInteger('choice_id')->unsigned();
            $table->timestamps();
        });

        Schema::table('answers',function (Blueprint $table){
            $table->foreign('question_id')
                    ->references('question_id')
                    ->on('questions');

            $table->foreign('choice_id')
                    ->references('choice_id')
                    ->on('choices');

            $table->foreign('user_id')
                    ->references('user_id')
                    ->on('schedules');
        });

When I do migrate I got

SQLSTATE[HY000]: General error: 
1005 Can't create table
`scheduler`.`answers` (errno: 150 "Foreign key constraint is incorrectly formed") 
(SQL: alter table `answers` add constraint
`answers_choice_id_foreign` foreign key (`choice_id`) references `choices` (`choice_id`))

PDOException::("SQLSTATE[HY000]: 
General error: 1005 Can't create table `scheduler`.`answers` (errno: 150 "Foreign key constraint is incorrectly formed")")
Ad

Answer

Now that you've matched the types up, the reason it's failing is the other key isn't indexed, a foreign key creates an index in the table but it also needs one in the other table.

Put the following in your choices migration:

$table->bigInteger('choice_id')->unsigned()->index(); 
Ad
source: stackoverflow.com
Ad