How to change primary key in laravel migration

Question

I'm trying to change the primary key on a table so I can use an other column as foreign key too.

Here's the migration for the creation

public function up()
{
   Schema::create('blacklists', function (Blueprint $table) {
        $table->id();
        $table->integer('adv_id')->default(0);
        // ...other columns
   });
}

Then, I've made an other migration to change the adv_id column type in order to prepare the addition of a foreign key

public function up()
{
    Schema::table('blacklists', function(Blueprint $table){
         $table->integer('adv_id')->unsigned()->change();
    });
}

Here's where I'm stocked

public function up()
{
     Schema::table('blacklists', function(Blueprint $table) {
         $table->dropPrimary('id');
         $table->integer('adv_id')->primary()->change();
     });
}

When I run the last migration, I got this error message

Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key (SQL: alter table `blacklists` drop primary key)

I think `dropPrimary` drops the index, not necessarily the column... see: https://stackoverflow.com/a/47270330/886824 and https://laravel.com/docs/9.x/migrations#dropping-columns — CD001, May 09 '22 at 11:10
but do I need to drop the column if I just want to use an other column as the index ? — skytorner, May 09 '22 at 12:08
The problem is that MySQL/MariaDB only allows `AUTOINCREMENT` on the `PRIMARY` - so you don't necessarily need to get rid of the column but you do need to get rid of the auto-increment. — CD001, May 09 '22 at 13:40
ok ! thanks for the tip ! that solved it ! If you can post it as the answer, i'll validate it — skytorner, May 09 '22 at 14:03

archille gael · Answer 1 · 2022-06-09T09:02:50.400

1

this work for me

public function up()
{
 Schema::table('blacklists', function (Blueprint $table) {
   $table->dropPrimary();
   $table->unsignedInteger('id')->change();
   $table->dropColumn('id');
   $table->uuid()->primary()->unique()->index()->first();
 });

edited Jun 09 '22 at 09:02

answered Jun 09 '22 at 08:24

archille gael

70
1
8

This produced an error for me: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'PRIMARY' – gboone Mar 09 '23 at 20:00
@gboone did you get annay solution ? – archille gael Mar 11 '23 at 04:00
@gboone if not i advise you to separate it into 2 migrations: one for dropping the primary key and the other one for setting the new primary key – archille gael Mar 11 '23 at 04:02

score 0 · Answer 2 · answered May 09 '22 at 11:53

0

You can try this because at a time only one auto-increment allow.

public function up()
{
 Schema::table('blacklists', function (Blueprint $table) {
    $table->dropPrimary('id');
    $table->integer('adv_id')->unsigned()->change();
 });
}

answered May 09 '22 at 11:53

Shravan Goswami

42
1
4

score 0 · Answer 3 · answered Mar 09 '23 at 20:10

This worked for me, I am showing multiple Schema blocks in the one migration, and am answering the question in general of how to change the primary key in laravel. For example, make a uuid field the primary:

use Iluminate\Support\Str;
use App\Blacklists;

public function up()
{
  Schema::table('blacklists', function (Blueprint $table) {
    $table->uuid('uuid')->first();
  });

  $blacklists = Blacklists::all();
  if($blacklists) {
    foreach($blacklists as $blacklist) {
      $blacklist->uuid = Str::uuid();
      $blacklist->update();
    }
  }

  Schema::table('blacklists', function (Blueprint $table) {
    $table->dropPrimary();
    $table->unsignedInteger('id')->change();
    $table->dropColumn('id');
  });

  Schema::table('blacklists', function (Blueprint $table) {
    $table->primary('uuid');
    $table->renameColumn('uuid', 'id');
  });

}

How to change primary key in laravel migration

3 Answers3