CODEWITHSUNDEEP
phplaravel
Can Geliş
Can Geliş

Reputation: 1474

Composite keys laravel schema builder

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

Upvotes: 19

Views: 26906

Answers (6)

nafischonchol
nafischonchol

Reputation: 88

You can try like this

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

If you have not installed doctrine/dbal package. Please install doctrine/dbal package first. Changing columns for table requires doctrine/dbal package. Run this command on the root directory of the framework:

composer require doctrine/dbal

Upvotes: 0

omarjebari
omarjebari

Reputation: 5519

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

Upvotes: 12

haleksandre
haleksandre

Reputation: 766

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'.

Upvotes: 7

psmail
psmail

Reputation: 53

Another option from Dayle Rees

Schema::create('example', function($table)
{
    $table->integer('id');
    $table->string('username');
    $table->string('email');
    $keys = array('id', 'username', 'email');
    $table->primary($keys);
});

Upvotes: 4

jah
jah

Reputation: 1305

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 :)

Upvotes: 22

Alexandre Danault
Alexandre Danault

Reputation: 8682

It seems you've hit something that is not currently possible with Laravel, since ->increments() already sets ->primary() , so when you add it yourself you end up with two PRIMARY clauses in the resulting SQL.

But you may want to try creating the table with the wrong primary key, dropping it, then recreating it:

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

Schema::table('kitchen', function($table)
{
    $table->dropPrimary('kitchen_id_primary');
});

Schema::table('kitchen', function($table)
{
    $table->primary(array('id', 'restaurant_id'));
});

Upvotes: 1

Related Questions