Vishal B
Vishal B

Reputation: 653

Remove primary key and auto-increment in database using migration in laravel

I have table with primary key and auto-increment field, I want make new migration to drop primary key index and also remove the auto increment field. How can i achieve this.

I created new migration as

public function up()
{
    Schema::table('tbl_message_read_state', function (Blueprint $table) {

    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_message_read_state', function (Blueprint $table) {

        $table->dropPrimary('message_id');
        $table->unsignedInteger('message_id');
    });
}

It gave me error in command as [Illuminate\Database\QueryException]
SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'message _id' (SQL: alter table tbl_'message_read_state' add 'message_id' int unsigned not null)

Whats Wrong ?????

Upvotes: 7

Views: 32582

Answers (7)

Adam
Adam

Reputation: 29079

This did it for me:

Schema::table('table_name', function (Blueprint $table) {
    // Make AI field `id` unsigned otherwise SQL 
    // will throw error when you try to remove it
    $table->integer('id')->unsigned()->change();

    $table->dropColumn('id');

    // If there was a foreign on message_id, make sure to remove it
    $table->dropForeign('table_name_message_id_foreign');

    $table->dropPrimary('message_id');
    $table->dropColumn('message_id');
}

Upvotes: 5

Vardges  Qeshishyan
Vardges Qeshishyan

Reputation: 1

https://laravel.com/docs/4.2/schema#dropping-indexes

  1. tabel_name
  2. column
  3. index $table->dropPrimary('users_id_primary');

Upvotes: -5

sturrockad
sturrockad

Reputation: 4560

The dropPrimary method only removed the primary key, not the column. You would have to do:

    /**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_message_read_state', function (Blueprint $table) {

        $table->dropPrimary('message_id');
        $table->dropColumn('message_id');
        $table->unsignedInteger('message_id');
    });
}

Or instead of dropping and re-creating the column, you can use change in Laravel 5.

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('tbl_message_read_state', function (Blueprint $table) {

        $table->dropPrimary('message_id');
        $table->integer('message_id')->unsigned()->change();
    });
}

Upvotes: 4

Kimsal San
Kimsal San

Reputation: 61

Use a simple drop column

$table->dropColumn('id');

Upvotes: 1

Vishal Varshney
Vishal Varshney

Reputation: 905

You can try this

$table->dropPrimary('id_primary');

Upvotes: 3

Rahul
Rahul

Reputation: 2474

Blueprint class offers dropPrimary methods that allow you to remove primary key.

public function down()
{
    Schema::table('table', function (Blueprint $table) {
        $table->dropPrimary();
        $table->unsignedInteger('id'); // for removing auto increment

    });
}

Upvotes: 11

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Drop the primary key:

$table->dropPrimary( 'id' );

Reference

Upvotes: 2

Related Questions