Laravel seed table with composite primary key

Question

I need to make a table with a composite primary key. I've been looking at multiple options to solve the problem to create an AUTO_INCREMENT field along with some other fields, and make them a composite primary key, but eventually I succeeded by doing it like this;

class CreateSpecificationTable extends Migration {

    public function up()
    {
        Schema::create('specification', function(Blueprint $table){
            $table->increments('specificationID');
            $table->integer('categoryID', false, true);
            $table->string('name', 100);

            $table->dateTime('created_at');
            $table->dateTime('updated_at')->nullable()->default(null);
            $table->dateTime('deleted_at')->nullable()->default(null);

            $table->foreign('categoryID')->references('categoryID')->on('categories');
        });

        DB::unprepared('ALTER TABLE specification DROP PRIMARY KEY, ADD PRIMARY KEY(specificationID, categoryID, name)');
    }

The model for this table is pretty simple:

class Specification extends Eloquent {

    protected $table = 'specification';
    protected $primaryKey = array('specificationID', 'categoryID', 'name');
}

And then the seeder looks like this:

class SpecificationSeeder extends Seeder {

    public function run()
    {
        Specification::create(array(
        'categoryID'=>1,
        'name'=>'size',
        ));

        Specification::create(array(
        'categoryID'=>2,
        'name'=>'resolution',
        ));

        Specification::create(array(
        'categoryID'=>1,
        'naam'=>'connection',
        ));
    }
}

However, when I run the php artisan db:seed command from CMD, I get the following error:

[ErrorException]
PDO::lastInsertId() expects parameter 1 to be string, array given

The strange thing is that I've built a lot of other tables in this app already this way, but this is the first one where the protected $primaryKey in the model consists of an array with fields, rather than having one single primary key.

Also, despite giving this error, the first 'seed' does get added in the database, but none after.

Answer 1

Eloquent does not support composite keys. You can make this work with one of two methods:

1) Use Schema Builder for the Seeder and leave your Migration as it is.

class SpecificationSeeder extends Seeder {

    public function run()
    {
        $data = array(
            array(
                'categoryID'=>1,
                'name'=>'size',
            ),
            array(
                'categoryID'=>1,
                'name'=>'size',
            ),
            array(
                'categoryID'=>1,
                'naam'=>'connection',
            )
        );

        DB::table('specification')->insert($data);
    }
}

2) Change your Migration, add a primary key and define a unique key. Leave the Seeder as it is

class CreateSpecificationTable extends Migration {

    public function up()
    {
        Schema::create('specification', function(Blueprint $table){
            $table->increments('id');
            $table->integer('specificationID');
            $table->integer('categoryID', false, true);
            $table->string('name', 100);

            $table->dateTime('created_at');
            $table->dateTime('updated_at')->nullable()->default(null);
            $table->dateTime('deleted_at')->nullable()->default(null);

            $table->unique( array('specificationID', 'categoryID', 'name') );

            $table->foreign('categoryID')->references('categoryID')->on('categories');
        });
    }
}

Answer 2

Laravel 4 does support composite keys. Also, its best to set your primary key correctly as well, not using the foreign() syntax.

As stated in the docs:

$table->primary('specificationID'); creates a primary key. However since you're using increments('id'); you already have a primary key as id. So your column specificationID is redundant unless you are actually storing an ID of a specification that is predefined and constant.

$table->primary(array('specificationID', 'categoryID', 'name')); creates a composite key

In general, I would try to keep your composite index as short as possible and only include columns if they are absolutely necessary. Do you really need to add name to your key? I realize for smaller applications this may not be as relevant, just trying to speak up for best practices.

Answer 3

The way I succeeded in having Laravel 5.1 working with composite keys. First define your schema as:

class CreateSpecificationTable extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('specification', function(Blueprint $table){
            $table->primary(array('specificationID', 'categoryID', 'name'));
            $table->integer('categoryID', false, true);
            $table->string('name', 100);
            $table->dateTime('created_at');
            $table->dateTime('updated_at')->nullable()->default(null);
            $table->dateTime('deleted_at')->nullable()->default(null);
            $table->foreign('categoryID')->references('categoryID')->on('categories');
        });
    }

Your model:

class Specification extends Eloquent {

    protected $table = 'specification';
    protected $primaryKey = array('specificationID', 'categoryID', 'name');
    public $incrementing = false; //important to avoid exception PDO::lastInsertId() expects parameter 1 to be string, array given
}

If you need to update or delete your model, then it's necessary to override the setKeysForSaveQuerymethod:

protected function setKeysForSaveQuery(\Illuminate\Database\Eloquent\Builder $query)
{
    if(is_array($this->primaryKey)) {
        foreach($this->primaryKey as $pk) {
            $query->where($pk, '=', $this->original[$pk]);
        }
        return $query;
    } else {
        return parent::setKeysForSaveQuery($query);
    }
}