Migration - Create a table that references a pivot table

Thanks @idew , In the early stages the tables a pretty bare, however I will be adding entities later once I figure out the complete schema. The other point is once I work out the models, the program flow will look something like this.

$game = Game::latest()->first();
$getFromDb  = $game->player()->turn()->action()

So if I can create the methods in the models for that flow to work, I don't really mind which way to go. At the moment i'm following my schema design.

Thanks @rodrigo.pedra , I missed that :)

However I still get this error:

Migrating: 2022_02_28_055839_create_turns_table
sh: 1: game_id: Permission denied
sh: 1: user_id: Permission denied

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1005 Can't create table `acquisitionboardgame_vue`.`turns` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `turns` add constraint `game_user_turns_foreign` foreign key (`game_id`, `gam
e_user_id`) references `game_user` (``, ``) on delete cascade)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:712
    708▕         // If an exception occurs when attempting to run a query, we'll format the error
    709▕         // message to include the bindings with SQL, which will make this exception a
    710▕         // lot more helpful to the developer instead of just the database's errors.
    711▕         catch (Exception $e) {
  ➜ 712▕             throw new QueryException(
    713▕                 $query, $this->prepareBindings($bindings), $e
    714▕             );
    715▕         }
    716▕     }

      +9 vendor frames
  10  database/migrations/2022_02_28_055839_create_turns_table.php:33
      Illuminate\Support\Facades\Facade::__callStatic()

      +32 vendor frames
  43  artisan:37
      Illuminate\Foundation\Console\Kernel::handle()

@muldev you might have typed something wrong when fixing the ->references() clause, as the error message says you are trying to map to empty columns.

Either way, I copied the migration into a test project and there is also a type mismatch in the foreign keys. Every foreign key should match the referenced key.

I fixed those, and you can try running this:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateTable extends Migration {
    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->unsignedBigInteger('director')
                ->comment('id of a player who initiated the game');
            $table->unsignedBigInteger('result_id')
                ->nullable()
                ->default(null)
                ->comment('at start game has no result');
        });

        Schema::create('game_user', function (Blueprint $table) {
            $table->unsignedBigInteger('user_id'); // <<< CHANGED HERE
            $table->unsignedBigInteger('game_id'); // <<< CHANGED HERE

            $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->unsignedBigInteger('game_id')
                ->nullable()
                ->default(null)
                ->comment('Game id that the turn belongs to');
            $table->unsignedBigInteger('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 ...');
            $table->smallInteger('piece_action')
                ->nullable()
                ->default(null)
                ->comment('Action taken with piece: 1) Played 2) ...');

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

    public function down()
    {
        Schema::dropIfExists('turns');
        Schema::dropIfExists('game_user');
        Schema::dropIfExists('games');
        Schema::dropIfExists('users');
    }
};

P.S.: I shortened some comments so the code fits nicely above

Thanks, @rodrigo.pedra I changed all unsignedBigInteger to unsignedInteger as there was still one error.

I thought that the keyword "increments" used bigInteger, looks like I was wrong :)

It's working great now.

@muldev well I didn't remember that.

These days I use:

• ->id() for primary keys (already adds auto_increments and primary key in MySQL)
• ->foreignId('column_name') for foreign keys columns (does not adds any constraints nor indices)

Both already uses UNSIGNED BIG INTEGER under the hood.

But good you got it working. Have a nice day =)

Hi @Tray2 ,

the pivot table has 2 IDs as primary key, which is why I used the array.

	 $table->primary(['game_id', 'user_id']);

hmm, looks like i'll have to reference the parent tables. I didn't think it would be such a problem referencing the pivot table .

@muldev I would probably not set a primary key for a pivot, but rather go with a unique index instead,