Ad

Laravel Migration Fails Multiple Primary Keys

- 1 answer

I am trying to create a Migration in Laravel but it fails saying I got multiple primary keys.

public function up()
{
    Schema::create('spins', function (Blueprint $table) {
        $table->integer('rid', true, true);
        $table->bigInteger('pid');
        $table->integer('result');
        $table->integer('bet');
        $table->timestamps();
        $table->primary(array('rid', 'pid'));
    });
}

The error:

SQLSTATE[42000]: Syntax error or access violation: 1068 Multipleprimary key defined 
(SQL: alter table `spins` add primary key `spins_rid_pid_primary` (`rid`, `pid`))      
Ad

Answer

The autoincrement of rid is the problem (second parameter in the line below).

$table->integer('rid', true, true);

If you are using InnoDB as MySQL engine it doesn't allow composite primary keys with an auto increment.

But if you change to the MyISAM engine it would be possible to do so.

  1. Add $table->engine = 'MyISAM'; to your Migration.

  2. Declare the rid field as a normal integer column

  3. Laravel doesn't provide a method to change existing columns so you need to run a raw SQL query: DB::statement('ALTER TABLE spins MODIFY rid INTEGER NOT NULL AUTO_INCREMENT');


public function up()
{
    Schema::create('spins', function (Blueprint $table) {
        $table->engine = 'MyISAM';
        $table->integer('rid')->unsigned();
        $table->bigInteger('pid');
        $table->integer('result');
        $table->integer('bet');
        $table->timestamps();
        $table->primary(array('rid', 'pid'));

        DB::statement('ALTER TABLE spins MODIFY rid INTEGER NOT NULL AUTO_INCREMENT');
    });
}
Ad
source: stackoverflow.com
Ad