Laravel 4: How do I create a non-primary auto incrementing column with migrations?

Question

I'm currently working on a Laravel 4 project consisting of a master server and many clients. The clients create data and send this to the master server. To avoid conflicts I am using UUID v4 as the primary key.

However, once the data is created on the server I want to assign a unique auto-incrementing integer so it is easier for users to identify the data. For example: Instead of speaking about item 5a8e896d-3ab4-48d2-9d39-faeb5227f012 a user can speak about item #24567

To keep my app managable I am using migrations, my current migration for this table looks like this:

public function up()
{
    Schema::table('items', function($table)
    {
        $table->create();
        $table->string('id')->primary(); //'id' For the purpose of keeping the ORM working, this field stores the UUID.
        $table->integer('number', true); //The human readable item number, the second parameter is true for auto-increment
        $table->text('otherdata');
        $table->timestamps();
    });
}

The problem is that Laravel automagically creates a primary key when defining auto-increment and so the migration ends up failing because there are two primary keys.

[Exception] SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined
  (SQL: alter table `items` add primary key items_id_primary(`id`)) (Bindings: array ())

Is there any way to have a table with a primary key and a seperate auto-incrementing field using Laravel 4 migrations.

Answer 1

I found the problem, Laravel seems to be creating a primary key for each auto_increment field. WHen i removed the primary key part it asked me to provide an index so I calling ->unique() on the migration, but this too did not work. Changing the return ' auto_increment primary key'; to return ' auto_increment unique'; solved my problem, although it is now hacked in the core, which of course is bad practice.

/**
 * Get the SQL for an auto-increment column modifier.
 *
 * @param  Illuminate\Database\Schema\Blueprint  $blueprint
 * @param  Illuminate\Support\Fluent  $column
 * @return string|null
 */
protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
{
    if ($column->type == 'integer' and $column->autoIncrement)
    {
        return ' auto_increment unique'; //return ' auto_increment primary key';
    }
}

Answer 2

trick is to add it outside of the Schema::create like this

Schema::create('payments', function(Blueprint $table)
{
   $table->string('primaryKey', 30);
   $table->primary('primaryKey');
   //...
});
DB::statement('ALTER Table tableName add id INTEGER NOT NULL UNIQUE AUTO_INCREMENT;');

Then redo migrations, the key will create with name id in the table tableName then you can access it like any other key.