Laravel migration: Multiple primary key defined (SQL: alter table `mediables` add primary key

Question

Error: Illuminate\Database\QueryException SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table mediables add primary key mediables_media_id_mediable_type_mediable_id_tag_primary(media_id, mediable_type, mediable_id, tag))

my migration :

  if (!Schema::hasTable('media')) {
            Schema::create(
                'media',
                function (Blueprint $table) {
                    $table->bigIncrements('id');
                    $table->string('disk', 32); 
                    $table->string('directory');
                    $table->string('filename');
                    $table->string('mime_type');
                    $table->string('aggregate_type');

                    $table->string('extension', 32); 
                    $table->integer('size')->nullable(false);
                    $table->timestamps(); 
                    $table->unique(['disk', 'directory', 'filename', 'extension']); 
                }
            );
        }

        if (!Schema::hasTable('mediables')) {
            Schema::create(
                'mediables',
                function (Blueprint $table) { 
                    $table->increments('media_id');
                    $table->string('mediable_type');
                    $table->integer('mediable_id')->unsigned();
                    $table->string('tag');
                    $table->integer('order')->unsigned(); 
                    $table->primary(['media_id', 'mediable_type', 'mediable_id', 'tag']);
                    $table->index(['mediable_id', 'mediable_type']);
                    $table->index('tag');
                    $table->index('order');
                    $table->foreign('media_id')
                        ->references('id')->on('media')
                        ->cascadeOnDelete();
                }
            );
        }

Answer 1

You cant use increments for foreign_key. Use unsignedBigIntger instead:

if (!Schema::hasTable('media')) {
            Schema::create(
                'media',
                function (Blueprint $table) {
                    $table->bigIncrements('id');
                    $table->string('disk', 32); 
                    $table->string('directory');
                    $table->string('filename');
                    $table->string('mime_type');
                    $table->string('aggregate_type');

                    $table->string('extension', 32); 
                    $table->integer('size')->nullable(false);
                    $table->timestamps(); 
                    $table->unique(['disk', 'directory', 'filename', 'extension']); 
                }
            );
        }

        if (!Schema::hasTable('mediables')) {
            Schema::create(
                'mediables',
                function (Blueprint $table) { 
                    $table->unsignedBigInteger('media_id');
                    $table->string('mediable_type');
                    $table->integer('mediable_id')->unsigned();
                    $table->string('tag');
                    $table->integer('order')->unsigned(); 
                    $table->primary(['media_id', 'mediable_type', 'mediable_id', 'tag']);
                    $table->index(['mediable_id', 'mediable_type']);
                    $table->index('tag');
                    $table->index('order');
                    $table->foreign('media_id')
                        ->references('id')->on('media')
                        ->cascadeOnDelete();
                }
            );
        }

Answer 2

The line $table->increments('media_id'); creates an autoincrementing id column and a primary key. So your subsequent primary key declaration attempts to create another primary key, which is not allowed.

An autoincrementing id column does not have to be the primary key, but it must be an index.

Try this:

    function (Blueprint $table) { 
      $table->increments('media_id');
      $table->string('mediable_type');
      $table->integer('mediable_id')->unsigned();
      $table->string('tag');
      $table->integer('order')->unsigned();

      /* create an index for the autoincrement column */
      $table->unique('media_id');  
      /* drop the primary key */
      $table->dropPrimary(); 
      /* add your new primary key */
      $table->primary(['media_id', 'mediable_type', 'mediable_id', 'tag']);

      $table->index(['mediable_id', 'mediable_type']);
      $table->index('tag');
      $table->index('order');
      $table->foreign('media_id')
                  ->references('id')->on('media')
                  ->cascadeOnDelete();
  }

I haven't tested this however.

If you can't get Laravel to cooperate with using a different PK than the autoincrement column, you may have to use an ordinary index. You can alter the indexes later with SQL statements if you really need your compound primary key. Something like this should work.

ALTER TABLE mediables 
     ADD UNIQUE INDEX media_id (media_id),
     DROP PRIMARY KEY,
     ADD PRIMARY KEY (media_id, mediable_type, mediable_id, tag);

Do this all in one statement.

Because your new primary key starts with the autoincrementing column, you actually can omit the UNIQUE index, giving

ALTER TABLE mediables 
     DROP PRIMARY KEY,
     ADD PRIMARY KEY (media_id, mediable_type, mediable_id, tag);