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 ?
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']);
});