Can't add foreign key constraint to pivot table

265 views Asked by At

Background:

I am building an application with Laravel for coaches to record their sport team's matches during a season. Currently, I am trying to create a pivot table, to map a Team to a Round (match).

Relationship overview:

A User has one Team, but the foreign key to the user table from the team table (user_id) is nullable, as an admin will create many Teams, but the user_id for a Team will be assigned at a later date.

A Team belongs to a User (coach).

There is a many to many relationship between a Round and a Team (this is where the pivot table comes in).

To map a Team to a Round, I have a round_team table with two foreign keys: round_id and team_id.

When trying to create the foreign key (team_id) on the round_team table, I get an error in the console, (after running php artisan migrate):

[Illuminate\Database\QueryException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `round_team` add constraint `round_team_team_id_foreign` foreign key (`team_id`) references `teams` (`id`))


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

I am assuming that this is somehow due to the teams.id being nullable... If this is the case, how do I go about adding this constraint successfully? Otherwise, what is causing the error?

Migrations:

Schema::create('users', function (Blueprint $table) {
        $table->increments('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('email')->unique();
        $table->enum('role', ['coach', 'admin'])->default('coach');
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });


Schema::create('teams', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('user_id')->unsigned()->nullable();
        $table->string('name')->unique();
        $table->string('slug')->unique();
        $table->timestamps();

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


Schema::create('rounds', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->timestamps();
    });


Schema::create('round_team', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('round_id')->unsigned();
        $table->integer('team_id')->unisigned();
        $table->timestamp('date')->nullable();
        $table->timestamps();

        $table->foreign('round_id')->references('id')->on('rounds');
        $table->foreign('team_id')->references('id')->on('teams');
    });
0

There are 0 answers