How to fix Incorrect Table Definition error in Laravel

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

If you faced the above error in Laravel, I will show you the reason why it happened and how it will be fixed.

The “Incorrect table definition” error can occur in Laravel due to many issues. I came across this error when I ran the php artisan migrate command to migrate the database schema in Laravel. While the error itself gives us a hint on why it occurs, but that’s not helpful enough to locate the root cause of the error.

The following is an example of the code which triggered the error in my case.

public function up()
{
    Schema::create('city_list', function (Blueprint $table) {
    $table->increments('id');
    $table->string('city');
    $table->integer('zip', 6)->unsigned();
    $table->integer('latitude');
    $table->integer('longitude');
    $table->timestamps();
});
}

As you can see, there is only one Auto incrementing column i.e. id. Whereas the error suggests there are more than one auto column.

The cause of the error is $table->integer('zip', 6)->unsigned(); line. Here I have specified the size of the integer, which is the cause the error.

The second parameter of the integer field is for auto increment. If it is specified, it sets the second parameter as true which represents auto Increment. Thus creating another auto incrementing column and this triggers the error.

If you want to set the length of the integer field, you can do that by adding length() modifier to set the length of the integer. Here’s how the correct code should be.

$table->integer('zip')->length(6)->unsigned();

That’s it adding the length modifier will fix the incorrect table definition error. The complete correct code should be the following:

public function up()
{
    Schema::create('city_list', function (Blueprint $table) {
    $table->increments('id');
    $table->string('city');
    $table->integer('zip')->length(6)->unsigned();
    $table->integer('latitude');
    $table->integer('longitude');
    $table->timestamps();
});
}

You should be aware that its not only integer() for which this error can occur. If you use any integer blueprint (bigInteger, mediumInteger, tinyInteger, smallInteger, etc…) with a second parameter other than 0. You are telling Laravel to make an integer with the auto_increment property set to true, this will the same “Incorrect table definition ” error.