Laravel foreign_key Incorrect table definition; there can be only one auto column and it must be defined as a key

Question

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateOrderProductTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('order_product', function (Blueprint $table) {
            $table->bigIncrements('order_id');
            $table->foreign('order_id')->references('id')->on('orders');
            $table->bigIncrements('product_id');
            $table->foreign('product_id')->references('id')->on('products');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('order_product');
    }
}


I expected to create a pivot table, but when I run the "php artisan migrate" it give me this: 

    SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

(SQL: create table order_product (order_id bigint unsigned not null auto_increment primary key, product_id bigint unsigned not null auto_increment primary key) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

What is wrong with my code? :(

I think `bigIncrements()` will also make the key primary and you can't have multiple primary keys in the same table. Although the error seems to be foreign key related, so I'm not a 100% sure. — PtrTon, Aug 27 '19 at 19:53
`bigIncrements()` makes Auto-incrementing `UNSIGNED BIGINT` (primary key) equivalent column. MySQL does not allow two primary keys to exists in one table.. I assume you need to use `$table->bigInteger('product_id');` which should make a `BIGINT` datatype. — Raymond Nijland, Aug 27 '19 at 19:54
Change the two `bigIncrements` to `unsignedBigInteger`. You are trying to create two auto increment column in a pivot. — dparoli, Aug 27 '19 at 20:01
I change bigIncrements to unsignedBigInteger and it works. Thank you very much! :) — serg11u, Aug 27 '19 at 20:17

score 0 · Answer 1 · answered Aug 27 '19 at 20:55

Leave the first line as $table->bigIncrements('id')->unsigned(); The remaining lines with bigIncrements change to bigInteger() instead.

public function up()
{
    Schema::create('order_product', function (Blueprint $table) {
        $table->bigIncrements('id')->unsigned();
        $table->bigInteger('order_id')->unsigned();
        $table->foreign('order_id')->references('id')->on('orders');
        $table->bigInteger('product_id')->unsigned();
        $table->foreign('product_id')->references('id')->on('products');
    });
}

Even better, you can use Eloquent's built in relationships instead of manhandling the intermediate pivot table.

dparoli · Answer 2 · 2019-08-27T21:56:09.933

With your migration you're trying to create two primary keys in the same table, this is the error you get.

When you declare a field as bigIncrements you're asking laravel to create a auto incrementing primary key; you have two of this declaration in the same table and this is not possible.

Seen that you want to create a pivot table you don't really need a primary key if you don't want to have a Model representing the intermediate table order_product.

To remove the error you have to use unsignedBigInteger for the two foreing keys, because the fields they are referring (the id columns of orders and products tables) are unsigned bigintegers i.e.:

public function up()
{
    Schema::create('order_product', function (Blueprint $table) {
        $table->unsignedBigInteger('order_id');
        $table->foreign('order_id')->references('id')->on('orders');
        $table->unsignedBigInteger('product_id');
        $table->foreign('product_id')->references('id')->on('products');
    });
}

In case you want to have a composite primary key on the two foreing keys you can write this inside your migration:

$table->primary(['order_id', 'product_id']);

This line ensure also that you don't get duplicate lines (order_id, product_id) in your table. You can have the same effect declaring a composite unique index:

$table->unique(['order_id', 'product_id']);

If you wants instead one primary key you can add a id column just writing this line to the migration above:

$table->bigIncrements('id');

But this is really useful only if you want a intermediate table Model.

BTW apart for the error with the bigIncrements the naming conventions used for the pivot table and the fields are already perfect.

@serg11u Please consider accepting and upvoting the answer if it solved your problem. — dparoli, Aug 28 '19 at 21:02

Laravel foreign_key Incorrect table definition; there can be only one auto column and it must be defined as a key

2 Answers2