Not able to add foreign key constraint to pivot table

2
votes

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?

2
mysqldatabaselaravellaravel-migrations
I recommend you to not using constraints on your db. instead, try to manage it in your code. - Morteza Rajabi
@MortezaRajabi and why is it so ? - Mayank Pandeyz
Managing such a table with constraints is hard, and you will have those errors every time you wanna add or delete something. - Morteza Rajabi
That's not a good practice. - Mayank Pandeyz

2 Answers

0
votes

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.

0
votes

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');