-1

I am having trouble adding a MEDIUMINT foreign key constraint to a table. I know it works, in the BASE TABLE the organization_id also references a MEDIUMINT. The first foreign key in the FOREIGN TABLE works, but the second does not.

Things I know for sure:

  • both the parent table and the child table have unsignedMediumInteger;
  • foreign column is created; and
  • the key is added after the parent table and foreign column creation.

Things I have tried:

  • changing $table->unsignedMediumInteger('customer_id'); to $table->mediumInteger('customer_id')->unsigned();
  • seperating the foreign key from the Schema::create('numberblocks') into Schema::table('numberblocks') on the same migration;
  • changing the datatype to INT, SMALLINT;
  • chaning the name from customer_id to c_id in the child table; and
  • setting the foreign key to its own migration.

BASE TABLE

        Schema::create('customers', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedMediumInteger('organization_id');
            $table->unsignedMediumInteger('customer_id');
            $table->string('customer_domain');
            $table->timestamps();

            // Foreign Keys
            $table->foreign('organization_id')->references('id')->on('organizations');
        });

FOREIGN TABLE

        Schema::create('numberblocks', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('group_id')->index();
            $table->unsignedMediumInteger('customer_id');
            $table->unsignedMediumInteger('order_id');
            $table->string('number', 15);
            $table->timestamps();

            // Foreign Keys
            $table->foreign('group_id')->references('id')->on('groups');
            $table->foreign('customer_id')->references('customer_id')->on('customers');
        });

Everything I have tried ends up with the same error:

SQLSTATE[HY000]: General error: 1005 Can't create table 'db'.'#sql-1a78_2a6'
(errno: 150 "Foreign key constraint is incorrectly formed")
(SQL: alter table 'numberblocks' add constraint 'numberblocks_customer_id_foreign'
foreign key ('customer_id') references 'customers' ('customer_id'))```

My question: Does anyone else know a way to get this working?

djvoyager
  • 35
  • 1
  • 6

1 Answers1

-1

yes it will as the error says

General error: 1822 Failed to add the foreign key constaint. Missing index for constraint 'numberblocks_customer_id_foreign' in the refere nced table 'customers'")

So try adding

$table->index(['customer_id']);

in the customers migration and will fix the issue

if not Kindly Comment below

ManojKiran A
  • 5,896
  • 4
  • 30
  • 43