Laravel migration fails multiple primary keys

Question

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`))      

Answer 1

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');
    });
}

Answer 2

Your primary key makes no sense.

You are adding a composite primary key to an auto incrementing column and another column. The auto incrementing column will already always be unique so you should just have only that be your primary key.

If you need pid to be unique, set rid to your primary key and add a unique key on pid.

Schema::create('spins', function (Blueprint $table) {
    $table->increments('rid');
    $table->bigInteger('pid');
    $table->integer('result');
    $table->integer('bet');
    $table->timestamps();
    $table->unique('pid');
});

If for some reason you do need your primary key to include rid and pid, this seems to work for me.

CREATE TABLE `spins` (
  `rid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `pid` BIGINT(20) NOT NULL,
  `result` INT(11) NOT NULL,
  `bet` INT(11) NOT NULL,
  `created_at` TIMESTAMP NOT NULL,
  `updated_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`rid`, `pid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;