migrate:reset erring when dropping foreign key

posted 11 years ago

I'm having an issue when rolling back a migration that drops foreign keys. I'm creating the foreign keys in their own migration file to avoid any issues with the tables not being present and the migration itself works fine. However, the reset provides the following SQL error.

[Illuminate\Database\QueryException]                                                                                                                                                                
SQLSTATE[HY000]: General error: 1025 Error on rename of './tips@002dapi/authors_books' to './tips@002dapi/#sql2 20a-f4' (errno: 152) (SQL: alter table `authors_books` drop foreign key author_id)

Everything seems fine. I'll include a sample migration set that I'm using to isolate the problem. I'm kind of stumped though. Could anyone offer some insight?

2014_03_22_162022_create_table_authors.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTableAuthors extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('authors', function(Blueprint $table) {
			$table->bigIncrements('id');
			$table->string('name');
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::drop('authors');
	}

}

2014_03_22_162026_create_table_books.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTableBooks extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('books', function(Blueprint $table) {
			$table->bigIncrements('id');
			$table->string('title');
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::drop('books');
	}

}

2014_03_22_162034_create_table_authors_to_books.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTableAuthorsToBooks extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('authors_books', function(Blueprint $table) {
			$table->unsignedBigInteger('author_id');
			$table->unsignedBigInteger('book_id');
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::drop('authors_books');
	}

}

2014_03_22_162220_add_fk_author_book.php

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddFkAuthorBook extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::table('authors_books', function(Blueprint $table) {
			$table->foreign('author_id')->references('id')->on('authors');
			$table->foreign('book_id')->references('id')->on('books');
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::table('authors_books', function(Blueprint $table) {
			$table->dropForeign('author_id');
			$table->dropForeign('book_id');
		});
	}

}

Last updated 2 years ago.

marwelln

replied 11 years ago

Solution

Your foreign keys is named table_fields_foreign. So in your case you would want this:

    Schema::table('authors_books', function(Blueprint $table) {
        $table->dropForeign('author_books_author_id_foreign');
        $table->dropForeign('author_books_book_id_foreign');
    });

Last updated 2 years ago.

visualasparagus

replied 11 years ago

Thanks for the response. The table is actually 'authors_books', but if I correct that it works fine.

It's also clearly indicated in the documentation, but I just missed it.

http://laravel.com/docs/schema#dropping-indexes

This still seems strange to me though. If Schema creates the foreign key / index, then I would expect it should drop it using the same name. That Schema actually creates an index called 'authors_books_author_id_foreign' when I pass it 'author_id' that's fine, but then I would expect it to maintain that intelligence when dropping it as well.

Last updated 2 years ago.

herlon214

replied 10 years ago

It don't work to me :(

Here is my code:

\Schema::table('contract', function(Blueprint $table) {
    $table->dropForeign('contract_product_id_foreign');
    $table->removeColumn('product_id');
});

What is wrong?

Last updated 10 years ago.

dpk3062

replied 10 years ago

Don't use the foreign table in the name, only the current table. It seems they changed the format from last year. Reread the documentation from visualasparagus's link carefully.

I don't know why Laravel does this. It certainly isn't expected behavior from someone new to databases and it's not clearly documented.

gummibeer

replied 8 years ago

In laravel 5.1 (don't know since which version) it's also possible to let laravel create the key name by putting the column(s) into an array, like:

Schema::table('authors_books', function(Blueprint $table) {
    $table->dropForeign(['author_id']);
    $table->dropForeign(['book_id']);
});

Moderators