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.

Remul · Accepted Answer · 2019-09-23 09:41:38Z

3

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');
});

answered Sep 23, 2019 at 9:41

Remul

8,3221 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shun1906 · Accepted Answer · 2020-07-28 19:37:12Z

1

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');
 });

answered Jul 28, 2020 at 19:37

Shun1906

313 bronze badges

Comments

zahra_oveyedzade · Accepted Answer · 2020-04-07 05:59:08Z

0

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

answered Apr 7, 2020 at 5:59

zahra_oveyedzade

1,23014 silver badges21 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related