add auto incrementing id in a table with existing primary key

Question

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

You can't do it with `bigIncrements`. That will attempt to set it to primary — aynber, May 15 '20 at 11:59

score 1 · Accepted Answer · answered May 15 '20 at 12:04

1

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

answered May 15 '20 at 12:04

Huy Trịnh

733
3
11

I want to do it with laravel, sir – Clarance Liberiste Ntwari May 15 '20 at 12:12

score 0 · Answer 2 · answered May 15 '20 at 12:02

0

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.

answered May 15 '20 at 12:02

aynber

22,380
8
50
63

1

great, you may short it to : $table->->unsignedBigInteger('columnName', true)->index(); – OMR May 15 '20 at 12:06
even not working here is the error `SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table \`books\` add \`id\` bigint unsigned not null auto_increment primary key)` – Clarance Liberiste Ntwari May 15 '20 at 12:11
How odd. There's no primary on any of the function definitions, even going all the way through. You did remove the bigIncrements line, correct? – aynber May 15 '20 at 12:22
yes @aynber i removed it , see `$table->unsignedBigInteger('id',true)->index();` – Clarance Liberiste Ntwari May 15 '20 at 12:34
No idea, then. You might need to do with manually with `DB::statement("alter table \`books\` add \`id\` bigint unsigned not null auto_increment, add index id_idx(id)");` – aynber May 15 '20 at 14:11

add auto incrementing id in a table with existing primary key

2 Answers2