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 Team
s, 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');
});