Have you encountered General error: 1215 Cannot add foreign key constraint in Laravel ?? When you are trying to add foreign key constraint from migration it generally shows this error. When i was trying it, I encountered it many times with different possible reasons.
It will also fix an errors like : “Foreign key constraint is incorrectly formed”.
The list of possible reasons when you are unable to create your foreign key constraints when creating migration are :-
- Foreign key is not an index.
- Data type of primary key and foreign key is not similar.
- Attribute of primary and foreign key doesn’t match.
- Order of created migration files.
Lets see each of the possible reasons in more detail.
1. Foreign Key is not an Index
To make foreign key you must have to make the key an index to view them in indexes. You can trigger index by adding index in migration like below :-
|
1 |
$table->integer('category_id')->index(); |
2. Data Type of Foreign Key and Primary Key are not Similar
If the data type of primary key is an integer int then data type of foreign key should also be int . It doesnot matter if the size are same or not. But data type must be same otherwise it will throw the same error.
3. Attribute of Foreign Key and Primary Key Doesn’t Match
If we are working on core php or procedural php it doesnot matter since we create all the table and keys by our own. Generally we neglect such issues.
But, while we are creating databases through laravel using migration, an unsigned attribute is added with primary key, this was the place where i was stuck. So in order for foreign key to be created, it should also be unsigned.
Best way to solve this issue would be first creating an unsigned integer and making it an index then making it foreign key like the code below :-
|
1 2 |
$table->unsignedInteger('category_id')->index(); $table->foreign('category_id')->references('id')->on('tbl_categories')->onDelete('restrict')->onUpdate('cascade'); |
4. Order of Migration Files in Laravel
If you have noticed when you create the migration it always be a long name such as,
2014_10_12_000000_create_users_table.php
which is stored in database/migrations folder in laravel. If you check avobe string first you will see 2014 is the date and then 10 seperated by underscores is the month while 12 is the day and all 00 00 00 are hours : minutes : seconds which shows when this file have been created.
When you migrate all the collection of the migration it migrates in an ascending order so in the cases like,
Categories migration should be created first before the products similarly user type should be created before the users migration so that migration will be created in an order without any problem.
So, you have an existing project where you have already created all the migration ? Just rename the migration files.
Take a look below :-
Users table migration – 2014_10_12_000000_create_users_table.php
User types table – 2017_08_21_015642_create_user_types_table.php
In this case migration fails if i just rename my user types migration to
2014_09_21_000000_create_user_types_table.php
Note : – After you rename the migration files make sure you do composer dump-autoload.
Then it will migrate the table and set the proper foreign keys without any issues. I hope you got the general idea in this post what are the possible reasons when migration fails.
These are the possible reasons i encountered when setting up foreign key and creating an migration hope it helps you as well.
Leave a Reply