0

We want to assign the RequestTypeID in the Request table to the RequestTypeID column in the DefRequestType table as a foreign key, but we get the following error.

 SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `Request` add constraint `request_requesttypeid_foreign` foreign key (`RequestTypeID`) references `DefRequestType` (`RequestTypeID`))

We fixed the problem by fixing the table engine as MyISAM, but we want to use the table engine as InnoDB. Also, we are trying the migration process in Laravel 8 version. First I create the DefRequestType table, then I create the Request table. The reason I later changed the value of the DefRequestTypeID column to bigIncrements in the DefRequestType table was to assign two primaryKeys in the DefRequestType table. I also wanted to be able to give the DefRequestTypeID column an additional autoIncrement property.

Request table

Schema::create('Request', function (Blueprint $table) {
   $table->bigIncrements('RequestID');
   $table->unsignedBigInteger('RequestTypeID')->nullable(); <----------------------------
   $table->unsignedInteger('CustomerID')->nullable();
   $table->unsignedInteger('ApplicationID')->nullable();
                
   $table->foreign('RequestTypeID')
         ->references('RequestTypeID')
         ->on('DefRequestType');
   $table->foreign('ContentID')
         ->references('ContentID')
         ->on('Content');
});

DefRequestType table

Schema::create('DefRequestType', function (Blueprint $table) {
   $table->unsignedInteger('DefRequestTypeID');
   $table->unsignedBigInteger('RequestTypeID'); <----------------------------
   $table->string('RequestDefinition');
   $table->integer('Status')->default(1);
   $table->integer('OldID');
   $table->primary(['DefRequestTypeID','RequestTypeID']);
});

Schema::table('DefRequestType', function (Blueprint $table) {
   $table->bigIncrements('DefRequestTypeID')->change();
});

We would appreciate it if you could help.

fhidiroglu
  • 145
  • 2
  • 14
  • Firstly the naming conventions for tables and the mix between big integers and ordinary integers makes this way more confusing than it have to be. Secondly, why do you create DefRequestTypeId or DefRequestType table and after change it to be primary key? – mrhn Feb 17 '21 at 11:44
  • You are right in the naming of the table, there is a non-standard usage, the people who previously worked on the project created this way and unfortunately, changing it at this stage will make the project difficult. The reason I set the DefRequestTypeID column as bigIncrements later was that I wanted to assign more than one primaryKey and I was able to set the DefRequestTypeID column as autoIncrement. – fhidiroglu Feb 17 '21 at 13:28

1 Answers1

1

The problem is on the table DefRequestType the column DefRequestTypeID is an ordinary integer. On the foreign key the RequestTypeID is an big integer.

To solve this either change RequestTypeID to ordinary integer or DefRequestTypeID to a big integer.

EDIT

I would change this to the following.

 $table->unsignedBigInteger('DefRequestTypeID');
mrhn
  • 17,961
  • 4
  • 27
  • 46
  • Sorry I guess I couldn't express myself. I am changing the DefRequestTypeID field in the DefRequestType table to a big integer below. My problem is to map the RequestTypeID column in the Request table to the RequestTypeID column in the DefRequestType table. Also, as you said, I changed RequestTypeID columns to integer and tried again, I encountered the same error. – fhidiroglu Feb 17 '21 at 12:26
  • Due to the bigincrements changes i feel that DefRequestTypeID should be an big integer in the Request table – mrhn Feb 17 '21 at 12:29
  • Unfortunately, we tried to run the code as you said, but we encountered the same error. Likewise, we assigned unsignedBigInteger type in RequestTypeID columns, but the error did not change. – fhidiroglu Feb 17 '21 at 13:19