laravelpostgresqllaravel-migrations

How to add new field into unique index on Postgres with migration?


I have laravel 12/ postgresql 17 app and in a table with 2 fields unique field :

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('action_id')->constrained('actions')->cascadeOnDelete();
    $table->unsignedSmallInteger('number');
    $table->string('name');
    $table->timestamps();

    $table->unique(['action_id', 'number']);
});

and I need to add new type field which also must be in unique index. I try to solve it with next migration :

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

        if($this->hasIndex("posts", 'posts_action_id_number_unique')) {
            echo '::TRY DELETE::'."<br>";
            $table->dropIndex('posts_action_id_number_unique'); // Drops index 'geo_state_index'
        }
        $table->unique(['action_id', 'type', 'number']);
    });
}


private function hasIndex(string $table, string $column): bool
{
    $indexes = Schema::getIndexes($table);
    echo count($indexes).'::$indexes::'.print_r($indexes,true);

    foreach ($indexes as $index) {
        if ($column === $index['name']) {
            return true;
        }
    }

    return false;
}

/**
 * Reverse the migrations.
 */
public function down(): void
{
    Schema::table('posts', function (Blueprint $table) {
        $table->dropIndex(['action_id', 'type', 'number']); // Drops index 'geo_state_index'
    });
}

But I got thiserror :

  SQLSTATE[2BP01]: Dependent objects still exist: 7 ERROR:  cannot drop index posts_action_id_number_unique because constraint posts_action_id_number_unique on table posts requires it
HINT:  You can drop constraint posts_action_id_number_unique on table posts instead. (Connection: pgsql, SQL: drop index "posts_action_id_number_unique")

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:822
    818▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    819▕                 );
    820▕             }
    821▕
  ➜ 822▕             throw new QueryException(
    823▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e

The hint seems rather strange for me as posts_action_id_number_unique refs to itself and I do not see how to fix it ?

How to fix my code to make it working ?


Solution

  • Similar to how you build your unique index via:

    $table->unique(['action_id', 'number']);
    

    you can also drop it and create a new one via:

    Schema::table('posts', function (Blueprint $table) {
        $table->dropUnique(['action_id', 'number']);
    
        $table->unique(['action_id', 'type', 'number']);
    });