CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-5.1
Vinay Raghu
Vinay Raghu

Reputation: 1279

Laravel Eloquent create composite key that uses autoincrements

I have a migration like below 

        $table->increments('id');
        $table->integer('order_num');
        $table->timestamps();

The primary key should be a combination of id and order_num. If I put the below code, it says I'm trying to add 2 primary keys (because the increments is also a primary key). Is there a way to tell eloquent that I'm trying to bunch the two together

        $table->primary(['course_id', 'order_num']);

Upvotes: 5

Views: 2207

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40919

I assume that you're using MySQL.

First of all, the default MySQL engine InnoDB doesn't allow composite primary keys where one of the fields would be autoincrement. The engine that allows that is MyISAM, so the first thing you need to do is to change the engine for the table.

You can do that by adding the following to your table definition:

$table->engine = 'MyISAM';

The next issue you need to tackle is the fact that Laravel's schema builder assumes, that the column you set as autoincrement is your primary key. That;s why you're getting the error about primary key existing already - Laravel already made the id field the primary key.

To solve this issue, you need to declare id field as normal integer column, define all other columns and at the end declare the composite primary key. It could look like that:

$table->integer('id');
$table->integer('order_num');
$table->timestamps();
$table->primary(['course_id', 'order_num']);

This way you have created a composite primary key. The only thing that is missing is the autoincrement attribute on id column. Laravel's schema builder doesn't provide methods to alter existing columns, that's why you'll need to run raw SQL queries. The query you need is:

DB::statement('ALTER TABLE my_table MODIFY id INTEGER NOT NULL AUTO_INCREMENT');

To sum up, your migration's up() method could look like following:

public function up()
{
  Schema::create('my_table', function (Blueprint $table) {
    $table->engine = 'MyISAM';

    $table->integer('id');
    $table->integer('order_num');
    $table->timestamps();
    $table->primary(['course_id', 'order_num']);
  });

  DB::statement('ALTER TABLE my_table MODIFY id INTEGER NOT NULL AUTO_INCREMENT');
}

Upvotes: 8

Related Questions