Not able to add foreign key constraint to pivot table

Question

I am trying to create a pivot table, with foreign keys, this is the migration I have made in Laravel:

public function up()
{
    Schema::create('player_position', function (Blueprint $table) {
        $table->integer('player_id')->unsigned()->index();
        $table->integer('position_id')->unsigned()->index();

        $table->foreign('player_id')->references('id')->on('players')->onDelete('cascade');
        $table->foreign('position_id')->references('id')->on('positions')->onDelete('cascade');
    });
}

But, I get an error:

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table player_position add constraint player_position_posi tion_id_foreign foreign key (position_id) references positions (id) on delete cascade)
                                                                         [PDOException]                                                        
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

I have read that usually foreign key constraint errors are about not assigning unsigned to fields, or already having records in the DB, but my DB is empty, and I my fields have unsigned, so don't know what is the problem?

I recommend you to not using constraints on your db. instead, try to manage it in your code. — Morteza Rajabi, Sep 25 '17 at 11:36
Managing such a table with constraints is hard, and you will have those errors every time you wanna add or delete something. — Morteza Rajabi, Sep 25 '17 at 11:44

score 0 · Answer 1 · answered Sep 25 '17 at 11:39

0

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

For a field to be defined as a foreign key, the referenced parent field must have an index defined on it. And the datatype of both the column and its size must be the same.

I think you are violating some of the above rule.

answered Sep 25 '17 at 11:39

Mayank Pandeyz

25,704
4
40
59

But, they do have index defined on them – Leff Sep 25 '17 at 11:40
And what about the `datatype`? – Mayank Pandeyz Sep 25 '17 at 11:40
As well, they are both integers – Leff Sep 25 '17 at 11:42
are they both the same type and length of integer though?. So both unsigned and both 11 chars long for example. – ATechGuy Sep 25 '17 at 21:54
I had this issue. Changing the parent id columns from `$table->bigIncrements` to `$table->increments` (to match `$table->integer` on the pivot table) worked for me. – ellcub Feb 13 '20 at 11:42

score 0 · Answer 2 · answered Sep 25 '17 at 11:48

Remove ->index() method as it creates basic index while you want to add foreign key constraint which references primary key on another table.

 $table->integer('player_id')->unsigned();
 $table->integer('position_id')->unsigned();

 $table->foreign('player_id')->references('id')->on('players')->onDelete('cascade');
 $table->foreign('position_id')->references('id')->on('positions')->onDelete('cascade');

Not able to add foreign key constraint to pivot table

2 Answers2