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