Laravel Compound Primary Key Sowing Table

Question

I need to create a table with a composite primary key. I considered several solutions to the problem in order to create an AUTO_INCREMENT field along with some other fields and make them a composite primary key, but in the end I managed to do it as follows:

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 quite simple:

 class Specification extends Eloquent { protected $table = 'specification'; protected $primaryKey = array('specificationID', 'categoryID', 'name'); } 

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 

It is strange that I already created many other tables in this application, but this is the first where protected $primaryKey in the model consists of an array with fields, and not with one primary key.

In addition, despite providing this error, the first "seed" is added to the database, but nothing happens after that.

Answer 1

Eloquent does not support composite keys. You can do this job in one of two ways:

1) Use the chart builder for the seeder and leave your migration as 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 drill 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 supports composite keys. In addition, it is best to properly configure your primary key without using the foreign () syntax.

As stated in the docs:

$table->primary('specificationID'); creates a primary key. However, since you are using increments('id'); , you already have the primary key as id. Thus, your column specification identifier is redundant unless you actually store a specification identifier that is predefined and constant.

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

In general, I will try to keep your composite index as short as possible and include only columns if they are absolutely necessary. Do you really need to add a name to your key? I understand that for small applications this may not be so important, just trying to speak out for best practices.

Answer 3

How I managed to get Laravel 5.1 to work with compound 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, you must override the setKeysForSaveQuery method:

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