Maantje
Maantje

Reputation: 1801

Laravel migration fails multiple primary keys

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

Upvotes: 8

Views: 36728

Answers (3)

user1669496
user1669496

Reputation: 33068

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;

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 20

Ben Harold
Ben Harold

Reputation: 6432

You can't have multiple primary keys on a single table. You can have a composite primary key, which is a primary key made from two or more columns. Apparently Blueprint does not support creating composite keys, so you'll have to use the query builder if you want to use composite keys.

Otherwise you can just choose pid or rid as your primary key.

Upvotes: 0

Related Questions