为何添加唯一约束会移除structure.sql及数据库中的索引？

原因说明 #

PostgreSQL 中，执行 ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX 时，数据库会将指定的独立唯一索引转换为该唯一约束的支撑索引——这不是单纯的“移除”，而是索引的归属关系发生了变化：原本是独立的索引对象，现在成为唯一约束的从属组件，不再以独立索引的形式存在。因为唯一约束本质就是通过唯一索引实现的，PostgreSQL 不会允许同一列上存在完全重复的独立索引与约束支撑索引（无意义且浪费存储和写入资源）。

解决办法 #

如果确实需要同时保留独立唯一索引和唯一约束，不能直接复用已有索引创建约束，可通过以下两种方式实现：

方式1：先创建约束，再新增独立索引 #

修改第二个迁移脚本，先让 PostgreSQL 自动为约束生成支撑索引，再创建独立的唯一索引：

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

  def up
    # 创建唯一约束，PostgreSQL会自动生成对应支撑索引
    sql_constraints = <<~END_OF_SQL
      ALTER TABLE public.suppliers ADD CONSTRAINT supplier_company_id_and_name_unique UNIQUE (company_id, name);
      ALTER TABLE public.programs ADD CONSTRAINT programs_company_id_and_name_unique UNIQUE (company_id, name);
      ALTER TABLE public.collections ADD CONSTRAINT collections_supplier_id_and_name_unique UNIQUE (supplier_id, name);
    END_OF_SQL
    execute(sql_constraints)

    # 创建独立的唯一索引
    sql_indexes = <<~END_OF_SQL
      CREATE UNIQUE INDEX CONCURRENTLY idx_supplier_company_id_and_name_unique ON public.suppliers USING btree (company_id, name);
      CREATE UNIQUE INDEX CONCURRENTLY idx_programs_company_id_and_name_unique ON public.programs USING btree (company_id, name);
      CREATE UNIQUE INDEX CONCURRENTLY idx_collections_supplier_id_and_name_unique ON public.collections USING btree (supplier_id, name);
    END_OF_SQL
    execute(sql_indexes)
  end

  def down
    # 先删除独立索引，再删除约束
    sql_indexes = <<~END_OF_SQL
      DROP INDEX CONCURRENTLY idx_supplier_company_id_and_name_unique;
      DROP INDEX CONCURRENTLY idx_programs_company_id_and_name_unique;
      DROP INDEX CONCURRENTLY idx_collections_supplier_id_and_name_unique;
    END_OF_SQL
    execute(sql_indexes)

    sql_constraints = <<~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_constraints)
  end
end

方式2：复制原索引后，用副本创建约束 #

如果已经执行了第一个迁移（原索引已存在），可以先复制一份原索引，再用副本创建约束，保留原索引：

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

  def up
    # 复制原索引作为约束的支撑索引
    sql_copy_indexes = <<~END_OF_SQL
      CREATE UNIQUE INDEX CONCURRENTLY idx_supplier_constraint_support ON public.suppliers USING btree (company_id, name) INCLUDE (id);
      CREATE UNIQUE INDEX CONCURRENTLY idx_programs_constraint_support ON public.programs USING btree (company_id, name) INCLUDE (id);
      CREATE UNIQUE INDEX CONCURRENTLY idx_collections_constraint_support ON public.collections USING btree (supplier_id, name) INCLUDE (id);
    END_OF_SQL
    execute(sql_copy_indexes)

    # 用复制的索引创建约束
    sql_constraints = <<~END_OF_SQL
      ALTER TABLE public.suppliers ADD CONSTRAINT supplier_company_id_and_name_unique UNIQUE USING INDEX idx_supplier_constraint_support;
      ALTER TABLE public.programs ADD CONSTRAINT programs_company_id_and_name_unique UNIQUE USING INDEX idx_programs_constraint_support;
      ALTER TABLE public.collections ADD CONSTRAINT collections_supplier_id_and_name_unique UNIQUE USING INDEX idx_collections_constraint_support;
    END_OF_SQL
    execute(sql_constraints)
  end

  def down
    # 删除约束会自动删除对应的支撑索引
    sql_constraints = <<~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_constraints)
  end
end

注意事项 #

同一列上维护两个完全相同的唯一索引（约束支撑索引+独立索引）会增加写入开销，每次插入/更新操作都要同步维护两个索引，建议先评估是否真的需要保留两者。

内容的提问来源于stack exchange，提问作者svelandiag