Laravel cannot add foreign key constraint

Question

When I'm trying to set a foreign key constraint in laravel 5 with migrations I receive the error:

[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table rittenregistratie add co nstraint rittenregistratie_karakterrit_id_foreign foreign key (karakterrit_id) references karakterrit (id) on delete cascade) [PDOException] SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint D:\wamp\www>

But I have now idea why??? The order of migrating is right so why do I receive this error? The table rittenregistratie has a foreign key called karakterrit_id this is the primary key in the table karakterrit.

This is my migration rittenregistratie:

 public function up()
    {
        Schema::create('rittenregistratie', function (Blueprint $table) 
        {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->timestamps('datum');
            $table->integer('beginstand');
            $table->integer('eindstand');
            $table->text('van');
            $table->text('naar');
            $table->text('bezoekadres');
            $table->text('geredenroute');
            $table->integer('karakterrit_id')->default(1);
            $table->text('toelichting');
            $table->integer('kilometerszakelijk');
            $table->integer('kilomteresprive'); 

            $table->foreign('user_id')
                        ->references('id')
                        ->on('users')
                        ->onDelete('cascade');

            $table->foreign('karakterrit_id')
                        ->references('id')
                        ->on('karakterrit')
                        ->onDelete('cascade');    
        });
    }

possible duplicate of MySQL Cannot Add Foreign Key Constraint — jszobody
The error you are getting is a DB error. Which means you need to take a closer look at the DB to see why. See the answer on that other thread for tips on troubleshooting foreign key errors. — jszobody
Specifically: your karakterrit_id column isn't unsigned. I'm willing to bet the primary key is unsigned, and that's your issue. (Described in the other SO thread). — jszobody
It has nothing to do with Laravel. It has everything to do with the database requiring that a primary key column and a foreign key column match exactly. — jszobody

paudel paudel · Accepted Answer · 2016-02-14T12:33:48

Add

$table->integer('karakterrit_id')->unsigned()->default(1);

Have you created the users Table and karakterrit that you used in your query. If you have both tables, check the creation date of the migration files they both should be created before other table that references them.

Another problem might be MyISAM which does not support. Instead use InnoDB

DB::statement('ALTER TABLE categories ENGINE = InnoDB');

Laravel cannot add foreign key constraint

3 Answers