I have two tables: one is 'event_player_lists'
Schema::create('event_player_lists', function (Blueprint $table) {
$table->id();
$table->foreignId('event_id')->constrained('events')->cascadeOnDelete();
$table->unsignedInteger('player_imported_id')->index();
$table->string('role', 50);
$table->timestamps();
});
and another one is:
Schema::create('event_winner_players', function (Blueprint $table) {
$table->id();
$table->foreignId('event_id')->constrained('events')->cascadeOnDelete();
$table->unsignedInteger('player_imported_id');
$table->string('vote')->nullable();
$table->float('point');
$table->foreign('player_imported_id')->references('player_imported_id')- >on('event_player_lists')->cascadeOnDelete();
$table->timestamps();
});
Now, My question is.
How can I create a foreign key relationship between these two tables based on event_id,player_imported_id
.
Reason: Now as my all event player import id is the same, So when I delete the event_player_list of ID: 3 events then all the winner_player_lists are deleted. If I restrict this then also I can't delete any event player list.
Problem: When I delete event 3 player_list, due to the same import id they make the winner_player empty.
The solution I want: When I delete event 3 player_list, they should delete only the winner_players_list which has event_id 3.
I have tried this but nor working got sql error:
$table->foreign(['player_imported_id', 'event_id'])
->references(['player_imported_id', 'event_id'])
->on('event_player_lists')
->onDelete('NO ACTION');