CODEWITHSUNDEEP
phpmysqllaravellaravel-5.3
JasonDavis
JasonDavis

Reputation: 48933

Laravel Scheme Builder is adding auto_increment to all integer fields which makes it fail

When I try to use PHP Laravels Database Migration and schema builder I keep getting the error below on any tabvle that has an auto incrementing ID column and a regular user_id integer column.

The error below shows that the user_id column SQL is being generated with the value auto_increment on the user_id and my code does not tell it to do that anywhere!

I am using Laravel v5.3

My schema code is:

public function up()
{
    Schema::create('bookmark_tag_lists', function(Blueprint $table)
    {
        $table->increments('id', 10);
        $table->string('title', 100)->nullable();
        $table->string('slug', 100)->nullable();
        $table->text('description', 65535)->nullable();
        $table->string('list_icon', 200)->nullable();
        $table->text('tags', 65535)->nullable();
        $table->integer('user_id', 10)->unsigned();
        $table->dateTime('created_on');
        $table->dateTime('modified_on');
        $table->integer('parent')->default(0);
        $table->string('breadcrumb_path')->nullable();
        $table->integer('tag_count')->default(0);
        $table->integer('bookmark_count')->default(0);
        $table->integer('sort')->default(0);
        $table->integer('active')->default(1);
    });
}

Database error

[Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
  inition; there can be only one auto column and it must be defined as a key
  (SQL: create table `bookmark_tag_lists` (`id` int unsigned not null auto_in
  crement primary key, `title` varchar(100) null, `slug` varchar(100) null, `
  description` text null, `list_icon` varchar(200) null, `tags` text null, `u
  ser_id` int unsigned not null auto_increment primary key, `created_on` date
  time not null, `modified_on` datetime not null, `parent` int not null defau
  lt '0', `breadcrumb_path` varchar(255) null, `tag_count` int not null defau
  lt '0', `bookmark_count` int not null default '0', `sort` int not null defa
  ult '0', `active` int not null default '1') default character set utf8 coll
  ate utf8_unicode_ci)



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def
  inition; there can be only one auto column and it must be defined as a key

Upvotes: 4

Views: 7573

Answers (4)

Andre Toledo Gama
Andre Toledo Gama

Reputation: 152

In case your mistake is to have placed the number 10 inside the integer('name', 10), following the laravel documentation (https://laravel.com/docs/10.x/migrations#column-method-integer), you will see that you don't need it.

To fix just remove the value 10

This

$table->integer('user_id', 10)->unsigned();

To

$table->integer('user_id')->unsigned();

Two more comments

1 - UNSIGNED is an optional attribute for integers in MySQL used to define only positive numbers, check if it makes sense for you to use it.

2 - If you were to put any value different from 0 after the name of your column in the integer it will consider that it is a "true" value for auto_increment, that's why you have this error

Upvotes: 0

mapmath
mapmath

Reputation: 1532

In your code you are trying to give sizes for integer which leads to the error. If you change the following code, you won't get the same error.

Change this line $table->integer('user_id', 10)->unsigned(); to $table->integer('user_id');

Hope this will help

Upvotes: 2

Mikhail.root
Mikhail.root

Reputation: 832

Hi here's probably a solution. Lets look into this line which defines user_id value. 

$table->integer('user_id', 10)->unsigned();

I think you've meant that you need 10 byte unsigned integer writing this, but there's one thing about integer field type in Laravel 5.3. Lets look into this method definition, it expects up to 3 parameters to be passed in:

      public function Blueprint::integer($column, $autoIncrement = false, $unsigned = false) Illuminate\Support\Fluent 

      Create a new integer (4-byte) column on the table.
      Parameters: 
      string $column
      bool $autoIncrement
      bool $unsigned

So by passing in 10 php casts it to boolean and it makes it true, that is why it says you are trying to create more than one autoincrement fields!

Finaly the solution is:

    $table->integer('user_id', false,true);

with this you are asking to create not autoincrement field but still unsigned as you wish. But it creates 4byte unsigned integer with it so. There is even better solution:

   $table->bigInteger('user_id',false,true);

This one creates 8byte unsigned NOT autoincrement field in database.

Hope it'll help.

Upvotes: 5

JasonDavis
JasonDavis

Reputation: 48933

I fixed by adding $table->index('user_id'); to my tag_list table schema builder

Upvotes: 0

Related Questions