Reputation: 1801
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
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
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.
Add $table->engine = 'MyISAM';
to your Migration.
Declare the rid
field as a normal integer column
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
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