Foreign Key on Laravel Migration causing General Error 1215

Question

I'm trying to create multiple tables with foreign keys using Laravels migration. I have a problems migration table like so

Schema::create('problems', function($table)
        {
            $table->increments('id');
            $table->integer('created_by')->unsigned();
            $table->foreign('created_by')->references('id')->on('users');
            $table->integer('category')->unsigned();
            $table->foreign('category')->references('id')->on('categories');
            $table->timestamps();
        });

A categories migration

Schema::create('categories', function($table)
        {
            $table->increments('id');
            $table->string('category_name');
            $table->timestamps();
        });

My first foreign key into the users migration works fine but as soon as it hits the foreign key for the category id, it gives a

SQLSTATE HY000 General Error 1215 Impossible d'ajouter des contraintes d'index externe (SQL; alter table 'problems' add constraint problems_category_foreign foreign key ('category') references 'categories' ('id'))

(I can't properly read the French and I don't know why it gives me errors in French. I have not been able to find a way to change it as I am not French, nor can I understand it)

I don't understand why this would work for one and not the other when they are essentially the same thing.

score 2 · Accepted Answer · edited May 23 '17 at 12:00

2

You need to make sure that the order of the migration files work out. For example, you cannot create a problems table with a foreign key that references id on categories table before you actually create the categories table itself.

For the french error problem, look at this question.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 10 '15 at 06:51

Mahozi

353
1
2
10

score 1 · Answer 2 · answered Mar 02 '15 at 21:51

You have to change the migrations order

|-database/
|--migrations/
|---2015_02_02_141725_create_problems_table.php
|---2015_03_01_234626_create_categories_table.php

Should be:

|-database/
|--migrations/
|---2015_01_01_234626_create_categories_table.php
|---2015_02_02_141725_create_problems_table.php

Finally:

php artisan migrate

score 0 · Answer 3 · answered Feb 10 '15 at 01:57

0

I had to add the foreign key in a different migration file, fixing the issue.

answered Feb 10 '15 at 01:57

score 0 · Answer 4 · answered Feb 04 '16 at 16:18

I had the same problem days ago and the problem was the following:

on use $table->increments('id');, it generates a unsigned integer with length 10. and $table->integer('user_id') generate a not unsigned integer with length 11.

To fix it you might give up the unsigned and set all field's table that will be related to the others only as a auto increment field, like this:

$table->integer('id', true); // true represents the auto increment type

And, of course, set the $table->engine = 'InnoDB';.

Foreign Key on Laravel Migration causing General Error 1215

4 Answers4