Laravel Database Migration foreign key error

Question

I have two tables, transactions, and payments, which already exist from past migrations. When I try to make a pivot table between them, I get an error for the foreign key OF THE TRANSACTIONS ONLY. The other foreign key, on the "payments" table, is created just fine. This is absolutely baffling me, and none of the previous discussions I have found on this error have solved the problem.

The Error (for reference, MAccounting is the name of the database):

 [Illuminate\Database\QueryException] 
 SQLSTATE[HY000]: General error: 1005 Can't create table 'MRAccounting.#sql- 
 563_2d7' (errno: 150) (SQL: alter table `payment_transaction` add constrain 
 t payment_transaction_transaction_id_foreign foreign key (`transaction_id`) 
 references `transactions` (`id`)) 

*Despite what the error seems to say, the payment_transaction table is created successfully, along with the foreign key for the payments table; the only one that breaks is the foreign key for the transactions.

The Transaction Table:

 use Illuminate\Database\Migrations\Migration;
 use Illuminate\Database\Schema\Blueprint;
 class CreateTransactionsTable extends Migration {
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('transactions', function(Blueprint $table)
 {
 $table->increments('id');
 $table->string('name');
 $table->decimal('balance', 7,2);
 $table->integer('account_id');
 $table->integer('pending_id');
 $table->timestamps();
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::drop('transactions');
 }
 }

The payments table:

 use Illuminate\Database\Migrations\Migration;
 use Illuminate\Database\Schema\Blueprint;
 class CreatePaymentsTable extends Migration {
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('payments', function(Blueprint $table)
 {
 $table->increments('id');
 $table->integer('account_to');
 $table->integer('account_from');
 $table->decimal('amount',7,2);
 $table->timestamps();
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::drop('payments');
 }
 }

The Pivot table:

 use Illuminate\Database\Migrations\Migration;
 use Illuminate\Database\Schema\Blueprint;
 class CreatePaymentTransactionTable extends Migration {
 /**
 * Run the migrations.
 *
 * @return void
 */
 public function up()
 {
 Schema::create('payment_transaction', function(Blueprint $table)
 {
 $table->increments('id');
 $table->unsignedInteger('payment_id');
 $table->unsignedInteger('transaction_id');
 $table->timestamps();
 // $table->foreign('payment_id')->references('id')->on('payments');
 // $table->foreign('transaction_id')->references('id')->on('transactions');
 });
 Schema::table('payment_transaction', function(Blueprint $table){
 $table->foreign('payment_id')->references('id')->on('payments');
 $table->foreign('transaction_id')->references('id')->on('transactions');
 });
 }
 /**
 * Reverse the migrations.
 *
 * @return void
 */
 public function down()
 {
 Schema::drop('payment_transaction');
 }
 }

******************* GOT IT WORKING, BUT I STILL NEED TO FIGURE OUT HOW THIS HAPPENED ******** Unfortunately, a clean install solved this problem. That's fine and dandy, and I can keep developing now, but doing a clean install like that is not necessarily a convenience I have in a production environment. I need to figure out what caused this/how to recreate it.

• Kirill Fuchs: I suspect there is an error with some other migration because those migrations worked fine for me and its saying it can't create table MRAccounting. Check the migration for that table, maybe?
• theaceofthespade: MRAccounting is the name of the database (I've added a note of this to the question) - I think the reason it says it can't create the table is that it assumes that's what has happened when it's actually receiving the error failing to create the foreign key constrain. It is, however, interesting that a clean install worked for you.

Answer 1

For your pivot table use this migration:

public function up() {
 Schema::create('payment_transaction', function(Blueprint $table) {
 $table->increments('id');
 $table->unsignedBigInteger('payment_id');
 $table->foreign('payment_id')->references('id')->on('payments');
 $table->unsignedBigInteger('transaction_id');
 $table->foreign('transaction_id')->references('id')->on('transactions');
 });
}

Answer 2

you can fix all this mess by making payment_transaction.payment_id of type INT(10) unsigned. in this case payments.id INT(10) AUTO_INCREAMENT will be equal and your references will work.

• theaceofthespade: that was actually what I had it as originally; No dice. The UnsignedInteger method also does the same thing.
• Maximilian Prepl: look in database if they really are the same.
• Maximilian Prepl: i think that your table payment_transaction was different.
• theaceofthespade: I quadruple checked, because that's what everything I could find suggested. Plus, a clean install fixed it, which means that the only way they would have been different is if someone directly changed them in the database.
• Maximilian Prepl: the idea can be if one of your migrations failed, and shut down didn't made migration

Answer 3

Use the flag unsigned() on your primary and foreign keys.

• theaceofthespade: What is the supposed difference between that and $table->unsignedInteger('payment_id');

Answer 4

Why don't you try this:

$table->integer('payment_id')->unsigned();
$table->integer('transaction_id')->unsigned();

• theaceofthespade: What is the difference between that and $table->unsignedInteger('payment_id');?