1

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');
Shadow
  • 33,525
  • 10
  • 51
  • 64
T. Ikbal
  • 11
  • 1
  • What error did you get? – Shadow Aug 22 '23 at 12:44
  • ``` SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`2022_laravel_fantaweek`.`#sql-39b8_18e`, CONSTRAINT `event_wi nner_players_player_imported_id_event_id_foreign` FOREIGN KEY (`player_imported_id`, `event_id`) REFERENCES `event_player_lists` () (SQL: alter table `event_winner_players` add constra int `event_winner_players_player_imported_id_event_id_foreign` foreign key (`player_imported_id`, `event_id`) references `event_player_lists` (`player_imported_id`, `id`) on delete NO ACTION) ``` – T. Ikbal Aug 22 '23 at 12:56
  • Just out of curiosity, have you considered looking up what that error means and what the possible solutions are? – Shadow Aug 22 '23 at 12:59

0 Answers0