issue with dropping foreign key

Question

My foreign key relates to its own table. This was to produce posts with hierarchy.

Now when I try and drop the column in the database, it gives me this error:

1553 - Cannot drop index 'post_field_properties_parent_id_index': needed in a foreign key constraint

This is the code:

public function down()
{
        Schema::table( "post_field_properties", function( $table )
        {
            $table->dropForeign('parent_id');
            $table->dropColumn('parent_id');
        } );
}

The only way I seem to be able to do it, is to goto phpmyadmin and remove the foreign key itself. and then drop the column.

Answer 1

Just figured this out for my own project. When you are dropping a foreign key, you need to concatenate the table name and the columns in the constraint then suffix the name with "_foreign"

http://laravel.com/docs/5.1/migrations#foreign-key-constraints

public function down()
{
        Schema::table( "post_field_properties", function( $table )
        {
            $table->dropForeign('post_field_properties_parent_id_foreign');
            $table->dropColumn('parent_id');
        });
}

Answer 2

Here’s how to do it:

1. Log in to your database and lookup the name of the foreign key relationship. If you use phpmyadmin, go to the table, click the “Structure” tab, click the link “Relation View”

Important Note: to see “Relation View” make sure your tables “Storage Engine” is InnoDB if not review this Q&A

and wait a few seconds for it to load. Search for the field “Constraint name”. In my example this is: “contribution_copyright_id_foreign”

2. Go to the Laravel migration script (or create one). The trick is to first drop the foreign key relationship and then drop the column.

   public function down()

   {

        Schema::table('contribution', function(Blueprint $table){
   
            $table->dropForeign('contribution_copyright_id_foreign');
   
            $table->dropColumn('copyright_id');
   
        });
   

If you want to remove a table where a foreign key is present, you also first have to drop the foreign key relationship.

copied from here

Hope it help someone

Answer 3

I'm using Laravel 8, and turns out there's dropConstrainedForeignId that we can use for this. So instead of this one provided in another answer:

Schema::table( "post_field_properties", function( $table )
{
    $table->dropForeign('post_field_properties_parent_id_foreign');
    $table->dropColumn('parent_id');
});

You can write:

Schema::table( "post_field_properties", function( $table )
{
    $table->dropConstrainedForeignId('parent_id');
});

Answer 4

You may pass an array value which will automatically use the conventional constraint name when dropping: http://laravel.com/docs/5.1/migrations#foreign-key-constraints

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

Answer 5

You may pass an array of foreign keys inside the dropForeign() function, so Laravel will automatically concatenate the table name at the begining of the key name and 'foreign' at the end of it.

So please, try something like this code bellow in your down() function.

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

Answer 6

To check the name of the foreign key , first backup your database to .sql

there you will see the name of your foreign key like this :

...
KEY `employees_parent_id_foreign` (`parent_id`),
CONSTRAINT `employees_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `laravel_article` (`id`) ON DELETE CASCADE
...

in my case is laravel 5.4, it start by this format : tablename_columnname_foreign

so in your laravel (here i try to drop foreign key from employee table)

Schema::table("employees", function( $table )
{
    $table->dropForeign('employees_parent_id_foreign');
    $table->dropColumn('parent_id');
});

Answer 7

Try placing "_foreign" on the end of the column name. For example:

public function down()
{
        Schema::table( "post_field_properties", function( $table )
        {
            $table->dropForeign('parent_id_foreign');
            $table->dropColumn('parent_id');
        });
}