CODEWITHSUNDEEP
phplaravelpostgresqleloquent
LorenzSchaef
LorenzSchaef

Reputation: 1543

Replacing composite primary key with sequential id in laravel

I have a table with a composite primary key. I want to create a migration that removes that primary key and introduces a new column 'id' that is sequential and will become the new primary key.

Schema::table('cart_line', function (Blueprint $table) {
    $table->dropPrimary('cart_line_pkey');
    $table->increments('id');
    $table->primary('id');
});

This code throws the following error:

SQLSTATE[42P16]: Invalid table definition: 7 ERROR:  multiple primary keys for table "cart_line" are not allowed (SQL: alter table "cart_line" add primary key ("id"))

If I directly execute the following SQL, the primary key is being dropped and the code above executes without error:

ALTER TABLE cart_line DROP CONSTRAINT cart_line_pkey;

That's why I tried 

DB::statement('ALTER TABLE cart_line DROP CONSTRAINT cart_line_pkey;');

Schema::table('cart_line', function (Blueprint $table) {
    $table->increments('id');
    $table->primary('id');
});

but the the same error is thrown. The old primary key is not dropped, but there is no error until it tries to create the new one.

I'm using laravel 5.5 and postgres 9.6

Upvotes: 0

Views: 1675

Answers (2)

user320487
user320487

Reputation:

You need to do this in separate closures, so try this:

Schema::table('cart_line', function (Blueprint $table) {
    $table->dropPrimary('cart_line_pkey');
});

Schema::table('cart_line', function (Blueprint $table) {
    $table->increments('id');
});

The problem is the transaction has not committed yet, so making it two transactions should solve the problem.

Upvotes: 2

Mahdi Younesi
Mahdi Younesi

Reputation: 7509

Remove $table->primary('id') because $table->increments('id')makes the id column primary key

Schema::table('cart_line', function (Blueprint $table) {
    $table->dropPrimary('cart_line_pkey');
    $table->increments('id');
});

Upvotes: 6

Related Questions