Rails: Why does adding unique constraint removes the index in structure.sql file and db

Question

Initially this question was marked as duplicate of: Postgres unique constraint vs index

but Im not asking the differences of unique constraint vs unique index, that's why I updated the question title to make it more clear.

I would like to know why adding a unique constraint that uses a unique index just removes the indices, let me explain in detail, I have the following migration:

class AddUniqueIndexOnNamesForProgramsSuppliersCollections < ActiveRecord::Migration[5.2]
  disable_ddl_transaction!

  def up
    execute("CREATE UNIQUE INDEX CONCURRENTLY idx_supplier_company_id_and_name_unique ON public.suppliers USING btree (company_id, name);")
    execute("CREATE UNIQUE INDEX CONCURRENTLY idx_programs_company_id_and_name_unique ON public.programs USING btree (company_id, name);")
    execute("CREATE UNIQUE INDEX CONCURRENTLY idx_collections_supplier_id_and_name_unique ON public.collections USING btree (supplier_id, name);")
  end

  def down
    remove_index :suppliers, name: :idx_supplier_company_id_and_name_unique
    remove_index :programs, name: :idx_programs_company_id_and_name_unique
    remove_index :collections, name: :idx_collections_supplier_id_and_name_unique
  end
end

When I run the above migration I get the indices added to my structure.sql file. Then I have this other migration:

class AddUniqueConstraintsOnNamesForProgramsSuppliersCollections < ActiveRecord::Migration[5.2]
  disable_ddl_transaction!

  def up
    sql = <<~END_OF_SQL
      ALTER TABLE public.suppliers ADD CONSTRAINT supplier_company_id_and_name_unique UNIQUE USING INDEX idx_supplier_company_id_and_name_unique;
      ALTER TABLE public.programs ADD CONSTRAINT programs_company_id_and_name_unique UNIQUE USING INDEX idx_programs_company_id_and_name_unique;
      ALTER TABLE public.collections ADD CONSTRAINT collections_supplier_id_and_name_unique UNIQUE USING INDEX idx_collections_supplier_id_and_name_unique;
    END_OF_SQL

    execute(sql)
  end

  def down
    sql = <<~END_OF_SQL
      ALTER TABLE public.suppliers DROP CONSTRAINT supplier_company_id_and_name_unique;
      ALTER TABLE public.programs DROP CONSTRAINT programs_company_id_and_name_unique;
      ALTER TABLE public.collections DROP CONSTRAINT collections_supplier_id_and_name_unique;
    END_OF_SQL
    execute(sql)
  end
end

When I run the second migration indices get removed from my structure.sql file and from my DB and adds the constraints instead, so my guess is that it is replacing the indices with the constraints but I would like to have both, any idea?

This question's title was edited after it was marked as a duplicate. I think the original title made it seem like a duplicate, but the body of the question is actually asking something different. Might be worth considering re-opening it. — Daniel, Aug 04 '23 at 19:47
I'm guessing the answer is that Postgres doesn't want to expose implementation details. Not sure though. We're hoping someone else can speak to the question. — Daniel, Aug 04 '23 at 19:59
The end goal of these migrations is to safely add unique constraints, such that reads and writes to the tables don't get blocked while the migrations are running. The question is oriented towards ensuring that the migrations accomplish this goal. — Daniel, Aug 04 '23 at 20:01
From the manual: `If a constraint name is provided then the index will be renamed to match the constraint name. Otherwise the constraint will be named the same as the index.`. See https://www.postgresql.org/docs/current/sql-altertable.html — Frank Heikens, Aug 04 '23 at 20:06
Actually I thought the dupe I linked to answered this question too. The WHY is that because a unique constraint uses/requires the index as part of its implementation, then adding the constraint does not remove the index at all. PG developers probably concluded something like that if the constraint exists then it's redundant to **display** the index annotation and would lead to confusion when people tried to remove the index and got an error about the constraint requiring it, so once the constraint is added then the index is no longer displayed. — smathy, Aug 04 '23 at 20:23
@smathy if that's the case why when I try to remove the index it says it doens't exist? `PG::UndefinedObject: ERROR: index "idx_supplier_company_id_and_name_unique" does not exist` — svelandiag, Aug 04 '23 at 22:58
As I mentioned, because once you've created the constraint it would lead to confusion to allow people to remove the index independently (because the constraint requires the index under the hood). — smathy, Aug 05 '23 at 11:13
That all makes sense. Thank you. As a potential, future design or documentation consideration, I'd just point out that not displaying the index also led to confusion for us (as we became unsure whether the existing index would be used by the constraint), and it led us to question the safety of the migration (as we became unsure whether creating the constraint would build a new index non-concurrently). — Daniel, Aug 08 '23 at 17:50

score 0 · Answer 1 · answered Aug 05 '23 at 01:42

It is a rule in PostgreSQL that the name of a constraint is the same as the name of the index that implements it.

So if you write

ALTER TABLE tab ADD UNIQUE USING INDEX abc;

the constraint will be called abc, and if you write

ALTER TABLE tab ADD CONSTRAINT def UNIQUE USING INDEX abc;

the index abc will be renamed to def.

Rails: Why does adding unique constraint removes the index in structure.sql file and db

1 Answers1