I am trying to create a game with the tables listed below. There is a sample of the migration files here: Laravel.io example migration sample

The pivot table works well for players joining a game, I have seeded it and return data using Vue and each game lists all the players as it should.

The problem is creating a relation between the pivot table game_user (referred to as participant) and turns. Not sure if i'm going about this the right way, but my thinking is a player (game_user) plays many turns, a one-to-many relation.

I don't really want to create another primary key on the pivot table unless I have to, but I can't seem to connect them without errors been thrown.

So this foreign key will not work

$table->foreign(['game_id', 'game_user_id'], 'game_user_turns_foreign')->references(['game_id,user_id'])->on('game_user')->onDelete('cascade');

Laravel thows this error during the artisan migrate command

 php artisan migrate:fresh --seed

 Illuminate\Database\QueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1239 Incorrect foreign key definition for 'game_user_turns_foreign': Key reference and table reference don't match (SQL: alter table `turns` add constraint `game_user_turns_foreign` foreign key (
`game_id`, `game_user_id`) references `game_user` (`game_id,user_id`) on delete cascade)

Can someone help me out ?

Or maybe suggest another logical way to solve the relation.

The important thing to know is a turn must have a unique link to a game and a participant (game_user).

Here are the tables

Tables:

  Users Table                    Games Table   
   (shortened for example)        (shortened for example)

 -------------------              ------------------- 
| Entity: users     |            | Entity: games     |
 -------------------              ------------------- 
| id                |            | id                |
| first_name        |            | start_time        |
| last_name         |            | end_time          |
| display_name      |            | max_no_of_players |
| email             |            | director          |
 -------------------             | result_id         |
                                  ------------------- 
                                 
  Pivot Table referred           Turns Table                
  as participants or players      -------------------          
 -------------------             | Entity: turns     |                               
| Entity: game_user |             -------------------                                
 -------------------             | id                |                               
| user_id           |            | created_at        |                               
| game_id           |            | updated_at        |                               
 -------------------             | game_id           |                               
                                 | game_user_id      |                               
                                 | merge             |                               
                                 | purchase_array    |                       
                                 | piece_played      |                       
                                 | piece_action      |                       
                                  -------------------                        
 

CodePudding user response：

Looks Like I made a mistake creating reference ids as unsignedBigInteger. I thought that the keyword "increments" created unsignedBigInteger, but I was wrong.

So I changed all ids to unsignedInteger and that solved the problem.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class CreateTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
 
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('first_name', 25);
            $table->string('last_name', 25);
            $table->string('email', 100)->unique();
            $table->string('display_name');
        });
 
        Schema::create('games', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamp('start_time')->nullable();
            $table->timestamp('end_time')->nullable();
            $table->smallInteger('max_no_of_players')->default(2);
            $table->unsignedInteger('director')->comment('id of a player who initiated the game');
            $table->unsignedInteger('result_id')->nullable()->default(NULL)->comment('at start game has no result');
        });
 
        Schema::create('game_user', function (Blueprint $table) {
            $table->integer('user_id')->unsigned();
            $table->integer('game_id')->unsigned();
 
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('game_id')->references('id')->on('games')->onDelete('cascade');
 
            // Make both IDs a unique key
            $table->primary(['game_id', 'user_id']);
        });
 
         Schema::create('turns', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->unsignedInteger('game_id')->nullable()->default(NULL)->comment('Game id that the turn belongs to');
            $table->unsignedInteger('game_user_id')->nullable()->default(NULL)->comment('The participants unique id (game_id and user_id)');
            $table->boolean('merge')->default(false)->comment('Did a merge take place');
            $table->string('purchase_array')->comment('An array of the transaction history for this turn');
            $table->smallInteger('piece_played')->comment('An integer between 0 and 181 that represents the array node from A1 to N13 starting at 0(zero)');
            $table->smallInteger('piece_action')->nullable()->default(NULL)->comment('Action taken with piece: 1) Played 2) Replaced (could not play) 3) thrown away (dead piece)');
            
           
             // THIS FOREIGN KEY IS WHERE THE PROBLEM IS.
            $table->foreign(['game_id', 'game_user_id'], 'game_user_turns_foreign')->references(['game_id,user_id'])->on('game_user')->onDelete('cascade');
        });
}