Wednesday, March 29, 2017

[Laravel 5.4][Resolved] Foreign key constraint is incorrectly formed


 Error message:
 [Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table `mhhk`.`#sql-1b24_2  b` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter   table `news` add constraint `news_user_id_foreign` foreign key (`user_id`)   references `users` (`user_id`) on delete cascade)          

Firstly, you need to ensure you create the foreign key column, such as :
$table->unsignedInteger('user_id');
$table->foreign('user_id')->references('user_id')->on('users')->onDelete('cascade');
And then you should ensure the table and column you referenced to is created. For example, the schema builder with foreign() column type show above would create this sql :
ALTER TABLE `news` add constraint `news_user_id_foreign` foreign key(`user_id`) references 'users' (`user_id`) on delete cascade
In this case, it add a constraint named `news_user_id_foreign` to table `news`, it makes existing column `user_id` in table `news` references to column `user_id` in table `users`. To run this sql statement successfully, you need to ensure the table `users` and column `user_id` exists. It's better if make that column unique. An example :

$table->increments('user_id')->unique();
Also please ensure foreign key and column reference from foreign key are with same data type.

Do migration again. if it's still not work, reset migrate and then run dump-autoload via command prompt:
php artisan migrate:reset
composer dump-autoload

Reference

https://stackoverflow.com/questions/22367088/laravel-foreign-key-constraint-is-incorrectly-formed 
https://stackoverflow.com/questions/43103692/eloquent-foreign-key-constraint-is-incorrectly-formed-laravel

No comments :

Post a Comment