Dropping unsigned index foreign key in laravel. Syntax error or access violation: 1091 Can't DROP; check that column/key exists

Question

I created migration using the following

Schema::table('packages', function (Blueprint $table) {
     $table->integer('star_id')->unsigned()->index()->nullable()->default(null);
     $table->foreign('star_id')->references('id')->on('star');
});

in drop part

Schema::table('packages', function (Blueprint $table) {
      $table->dropForeign('star_id');
      //$table->dropIndex('star_id'); //also tried dropIndex
      $table->dropColumn('star_id');
 });

but it throws for index and foreign

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'packages_star_id_index': needed in a foreign key constraint (SQL: alter table packages drop star_id)

error for dropForeign

SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'star_id'; check that column/key exists (SQL: alter table packages drop foreign key star_id)

I cannot rollback because of the error.

Answer 1

You have to pass the foreign key name yourself or pass the column name in an array for laravel to automatically build it.

See here:

If the given "index" is actually an array of columns, the developer means to drop an index merely by specifying the columns involved without the conventional name, so we will build the index name from the columns.

// laravel assumes star_id is the foreign key name
$table->dropForeign('star_id'); 

// laravel builds the foreign key name itself e.g. packages_star_id_foreign
$table->dropForeign(['star_id']);

So in your case just pass the column in an array:

Schema::table('packages', function (Blueprint $table) {
    $table->dropForeign(['star_id']);
    $table->dropColumn('star_id');
});

Answer 2

Blockquote SQLSTATE[HY000]: General error: 1553 Cannot drop index 'packages_star_id_index': needed in a foreign key constraint (SQL: alter table packages drop star_id)

As per your error, index name was "packages_star_id_index". you should mention the proper index name in dropForeign function.

give it a try:

Schema::table('packages', function (Blueprint $table) {
      $table->dropForeign('packages_star_id_index');
      $table->dropColumn('star_id');
 });

Answer 3

Based on Laravel 7 documentation, for dropping foreign key this code will work:

$table->dropForeign('posts_user_id_foreign');

in which "posts" is the table name, "user_id" is foreign key name and "foreign" is the suffix.

There is also another method that only pass foreign key name in an array like this:

$table->dropForeign(['user_id']);

this picture is from Laravel website (v 7.x): here