Laravel 5.2 drop foreign key

Question

Good day,

I am trying to drop a foreign key in a Laravel 5.2 migration previously implemented like this:

EDIT: The creation of the id in the table (before using foreign key on it) was:

$table->integer('agent_rights_id')->unsigned()->nullable();

The foreign key:

Schema::table('agents', function (Blueprint $table){
        $table->foreign('agent_rights_id')->references('id')->on('agent_rights');
    });

My drop looks like this:

 Schema::table('agents', function (Blueprint $table){
        $table->dropForeign('agents_agent_rights_id_foreign');
        $table->dropColumn('agent_rights_id');
    });

I found out, that one must take the "real" index name and not the label - this I have already thought of in the previous snippet (as a reference to this question).

But this gives me the errors:

[Illuminate\Database\QueryException]                                                                                                                                                                                          
SQLSTATE[HY000]: General error: 1025 Error on rename of './{name}/agents' to './{name}/#sql2-2a6-1d8' (errno: 152) (SQL: alter table `agents` drop foreign key `agents_agent_rights_id_foreign`)  



[PDOException]                                                                                                                                  
SQLSTATE[HY000]: General error: 1025 Error on rename of './{name}/agents' to './{name}/#sql2-2a6-1d8' (errno: 152)

Researching this brings up no real solutions, only bug messages from MySQL...

Question: Do you guys anything about this, or what was wrong with my snippet?

I did what is told in this question before I created this post. This is not the same question, because I already did what is told there. — Mentenyia, Sep 08 '16 at 15:10
You are missing something because i ran the same in my project and it works fine. Anyway i remove duplicate and reopen it. I also double check your script again. See the Laravel documentation https://laravel.com/docs/5.2/migrations. — Manish, Sep 08 '16 at 17:57
you can also pass an array value which will automatically use the conventional constraint name when dropping like `$table->dropForeign(['agent_rights_id']);` — Manish, Sep 08 '16 at 17:59
@Manish I tried that, but sadly the same errors... Was I expected to add something special to the column before it was made a foreign key except unsigned()? — Mentenyia, Sep 09 '16 at 12:36

score 3 · Accepted Answer · answered Sep 09 '16 at 14:13

@Mentenyia This is compulsory for using unsigned() before creating Foreign key constraint. Please read the link https://laravel3.veliovgroup.com/docs/database/schema#foreign-keys .

Note: The field referenced in the foreign key is very likely an auto increment and therefore automatically an unsigned integer. Please make sure to create the foreign key field with unsigned() as both fields have to be the exact same type, the engine on both tables has to be set to InnoDB, and the referenced table must be created before the table with the foreign key.

So you should have to create like this:

$table->integer('agent_rights_id')->unsigned(); // Always create column before creating Foreign key constraint otherwise this will also give error.
$table->foreign('agent_rights_id')->references('id')->on('agent_rights');

After when you will be going to delete Foreign key No issue/ error will arise.

For droping Indexing Use this method table-name_column-name_index-type

$table->dropForeign('agents_agent_rights_id_foreign');

I still have the error when doing this, also when not using nullable() after unsigned()... But I will set up the whole database again from scratch to test it. I will answer then :) — Mentenyia, Sep 12 '16 at 09:49
It was the nullable() after unsigned() which did the trouble. Thank you for your help :) — Mentenyia, Sep 13 '16 at 08:17

Laravel 5.2 drop foreign key

1 Answers1