Laravel drop foreign Key in Migration

Question

I want to create a Migration which shall drop a table. I created the Migration like this:

Schema::table('devices', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('client_id')->nullable();
    $table->foreign('client_id')->references('id')->on('clients')->onDelete('cascade');
});

And now I try to drop it like this:

    Schema::table('devices', function (Blueprint $table) {
        $table->dropForeign('devices_client_id_foreign');
        $table->drop('devices');
    });

But I get following error:

In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'devices_client_id_foreign'; check that column/key exists (SQL:

alter table devices drop foreign key devices_client_id_foreign)

In PDOStatement.php line 144:

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'devices_client_id_foreign'; check that column/key exists


In PDOStatement.php line 142:

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'devices_client_id_foreign'; check that column/key exists

score 30 · Answer 1 · answered Aug 15 '18 at 15:30

30

You can use this answer. https://stackoverflow.com/a/30177480/8513937

Pass to dropForeign the column name as array. Internally, Laravel drops the associated foreign key.

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

answered Aug 15 '18 at 15:30

gandarrillas

411
3
5

This works when you didn't give it a different FKs name that Laravel sets by default. – Daniel Hernández Jul 21 '21 at 17:59

score 7 · Accepted Answer · answered Aug 15 '18 at 15:26

7

You just need to disable foreign key checks before you drop the table then enable them again after like this:

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
Schema::dropIfExists('devices');
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

answered Aug 15 '18 at 15:26

Jason Grim

413
4
7

If creating a migration that adds and drops particular columns this wont work. for example if you are adding a column to the users table this command drops the entire users table which could be catastrophic. better to use @laurazel8 response in the down function – DeveloperChris Jul 13 '23 at 02:31

score 6 · Answer 3 · answered Aug 15 '18 at 14:56

6

Try this ways...

  public function down()
 {
    Schema::dropIfExists('devices');
 }

//Or this
  public function down(){
    Schema::table('devices', function (Blueprint $table) {
        $table->dropForeign(['client_id']);
        $table->dropColumn('client_id');
        $table->drop('devices');
    });
  }

answered Aug 15 '18 at 14:56

Laurazel8

114
3

There is a foreign key I can't drop it that easily – Jon not doe xx Aug 15 '18 at 15:05

score 0 · Answer 4 · answered Aug 15 '18 at 14:48

0

Just drop the whole table (documentation):

Schema::drop('devices');

answered Aug 15 '18 at 14:48

Jonas Staudenmeir

24,815
6
63
109

Hi there is a foreign key Client_id so I cant drop it that easily – Jon not doe xx Aug 15 '18 at 15:05
`Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table `devices`)` – Jon not doe xx Aug 16 '18 at 06:23
Are there foreign keys in other tables that reference the `devices` table? – Jonas Staudenmeir Aug 16 '18 at 13:28
why drop the whole table in down method when we are not creating table in up method? – Sizzling Code Sep 24 '19 at 07:42
2

You can't drop the whole table while it has a foreign key. – shamaseen May 30 '20 at 07:58

Laravel drop foreign Key in Migration

4 Answers4

Linked