CODEWITHSUNDEEP
phpmysqllaravellaravel-migrations
Clarance Liberiste Ntwari
Clarance Liberiste Ntwari

Reputation: 4067

add auto incrementing id in a table with existing primary key

I am trying to add the auto-incrementing id to the existing table with its existing primary key

here is my code

initial migration for the table code

    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->string('ISBN')->index();
            $table->string('name')->index();
            $table->string('publisher');
            $table->string('level_levelName')->index();
            $table->string('provider_providerName')->index();
            $table->string('category_categoryName')->index();
            $table->text('description');
            $table->timestamps();
            $table->primary('ISBN');
            $table->foreign('provider_providerName')->references('providerName')->on('providers')->onDelete('cascade')->onUpdate('cascade');
            $table->foreign('level_levelName')->references('levelName')->on('levels')->onDelete('cascade')->onUpdate('cascade');
            $table->foreign('category_categoryName')->references('categoryName')->on('categories')->onDelete('cascade')->onUpdate('cascade');

        });
    }

add auto incrementint id to existing table code

    public function up()
    {
        Schema::table('books', function (Blueprint $table) {
            $table->bigIncrements('id');
        });
    }

what I am trying to do is add an auto-incrementing id to this existing table but it is giving me this error

 SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary keys defined (SQL: alter table 
`books` add `id` bigint unsigned not null auto_increment primary key)

Please can somebody help me? I don't want to remove table's primary key, I just want to add another auto-incrementing id which is not a primary key but it can be unique key

Upvotes: 0

Views: 2048

Answers (2)

Huy Trịnh
Huy Trịnh

Reputation: 753

This is not Laravel error. You can't have two auto increment column in mysql table.

However you can add to your model, get the biggest value of your field in the database and +1 then insert.

Other solution can be found here

Upvotes: 1

aynber
aynber

Reputation: 23011

You can't use bigIncrements as that will attempt to create it as a primary key. You can try this instead:

$table->bigInteger('id', true, true)->index();

Per the definition of bigInteger (public function bigInteger($column, $autoIncrement = false, $unsigned = false)) The second value is for auto increment and the third is signed/unsigned. From Mysql Innodb: Autoincrement non-Primary Key, the auto-increment key must be indexed.

Upvotes: 0

Related Questions