Composite keys laravel schema builder

Question

I need two composite primary keys and only one should be AUTO INCREMENT what I tried so far:

// first try 
Schema::create("kitchen", function($table) {
                $table->increments('id');
                $table->integer('restaurant_id');
                $table->primary(array('id', 'restaurant_id'));
                $table->string('name');
            });
// second try
 Schema::create("kitchen", function($table) {
                $table->increments('id');
                $table->integer('restaurant_id');
                $table->primary('restaurant_id');
                $table->string('name');
            });

None works. Error message:

[Exception]
SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary ke
y defined (SQL: alter tablekitchenadd primary key kitchen_restaurant_id
_primary(restaurant_id)) (Bindings: array (
))

The solution without Schema builder: first, I need to add two composite primary keys and then I need to make one of the AUTO INCREMENT but I think Schema builder can't do this.

Note: I can do this with SQL, I mean no problem with MySQL

Any suggestions?

Summary:

What I need is;

http://oi39.tinypic.com/es91ft.jpg

with Schema builder

Answer 1

You can do this with a little help from the unprepared method:

Schema::create("kitchen", function($table) {
    $table->increments('id');
    $table->integer('restaurant_id');
    $table->string('name');
});

DB::unprepared('ALTER TABLE `kitchen` DROP PRIMARY KEY, ADD PRIMARY KEY (  `id` ,  `restaurant_id` )');

This is tested, and works!

I know it's a little late, and you might have moved on, but someone else might come across this :)

Answer 2

It makes no sense to have the autoincrement as part of a composite primary key as it will be unique for every record anyway. If you want an autoincrement primary key and a unique composite key on eg 2 other columns such as 'restaurant_id' and 'name':

Schema::create("kitchen", function($table) 
{
    $table->increments('id');
    $table->integer('restaurant_id');
    $table->string('name');
    $table->unique(['restaurant_id', 'name']);
});

Answer 3

Eloquent's increments() sets the column as an unsigned integer. With that in mind you have multiple way of achieving your desired composite keys.

You could use either interger() or unsignedInteger() and setting the autoIncrements to true:

 $table->integer($column, $autoIncrement = false, $unsigned = false);

 $table->unsignedInteger($column, $autoIncrement = false);
 //Returns $this->integer($column, $autoIncrement, true);

Then you could tell Eloquent which are your primary keys via primary().

Your Schema::create() should look like this:

 Schema::create('kitchen', function($table) {
     $table->integer('id', true, true);
     $table->integer('restaurant_id')->unsigned(); //or $table->integer('restaurant_id', false, true);
     $table->foreign('restaurant_id')
           ->references('id')->on('restaurants')
           ->onDelete('cascade');
     $table->string('name');
     $table->primary(array('id', 'restaurant_id'));
 });

Assuming your Restaurant table is using $table->increments('id') this should make 'id' and 'restaurant_id' your primary keys while also matching 'restaurant_id' to the Restaurant table 'id'.