Why am I getting a Database Error on Migration ?

No matter what I do to my migration I keep getting this DB error:

[PDOException]                                                                                                                                         
  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

I think I understand entirely what the error means, however no amount of changes to my migration manages to fix the problem. I have tried to make "id" unique, set as primary, applied increments. I have also tried removing all the "unique" functions as well as all the "nullable". It always comes back the same.

I found this link on the web which apparently describes the problem and fix, but I am not sure what to do with it:https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Schema/Blueprint.php#L354

anyway, here is my Schema:create

Schema::create('customers', function(Blueprint $table)
    {
            $table->increments('id');
            $table->string('co_name',50);
            $table->integer('company_tel',10)->unsigned();
            $table->string('contact1_name',50);
            $table->integer('contact1_tel',10)->unsigned();
            $table->string('contact1_email',50);
            $table->string('contact2_name',50)->nullable();
            $table->integer('contact2_tel',10)->unsigned()->nullable();
            $table->string('street',50);
            $table->string('city',50);
            $table->string('province',50);
            $table->string('postcode',10);
            $table->string('website')->nullable();
            $table->string('notes')->nullable();
            $table->timestamps();
    });

As always Many Thanks !!

xingfucoder

11 years ago

Best Answer

Level 14

Hi @vincej try using one of the following option:

$table->integer('company_tel')->length(10)->unsigned();

Or:

$table->integer('company_tel', false, true)->length(10);

The second argument for integer() method is the flag for autoincrement and the third is for unsigned.

/**
  * Create a new integer column on the table.
  *
  * @param string $column
  * @param bool $autoIncrement
  * @param bool $unsigned
  * @return \Illuminate\Support\Fluent
  */
 public function integer($column, $autoIncrement = false, $unsigned = false)
 {
  return $this->addColumn('integer', $column, compact('autoIncrement', 'unsigned'));
 }

10 likes

vincej

11 years ago

Level 15

Heah Thanks a BIllion!!

I had a better look at the sql errors coming out and noticed that indeed everything with "integer" was being assigned as an auto increment - which was throwing the error.

I checked the Laravel manuals very carefully before posting and maybe I am blind, but I see no mention of this.

Thank you so much I am used to working with raw SQL and SQL WorkBench.

cheers !!

2 likes

xingfucoder

11 years ago

Level 14

You are welcome @vincej, in the source repository code of Laravel and the Framework you can find several of this methods and the comments that give you some extra information about the functionality of those methods.

1 like

Please or to participate in this conversation.