如何重命名TypeORM中一对一关系自动生成的默认索引？

TypeORM在处理一对一关系时，会自动创建格式为REL_+随机字符的唯一索引，我需要自定义这个索引的名称，同时避免生成重复的唯一索引，且保留外键约束。

我的实体代码 #

export const CompanySettingsEntity = new EntitySchema<ICompanySettingsEntity>({
    name: 'companySettings',
    tableName: 'companySettings',
    columns: {
        idCompanySettings: {
            type: 'smallint',
            primary: true,
            generated: true,
            unsigned: true,
            comment: 'Chave primária',
        },
        idCompany: {
            type: 'smallint',
            unsigned: true,
            comment: 'Chave da Empresa',
        },
        radarCulturalImage: {
            type: 'varchar',
            length: 100,
            collation: 'utf8mb4_unicode_ci',
            charset: 'utf8mb4',
            nullable: true,
            default: () => 'NULL',
            comment: 'Imagem customizada da empresa usada no radar',
        },
        disableCountry: {
            type: 'enum',
            enum: ['true', 'false'],
            default: 'false',
            comment: 'Desabilita o país',
        },
        disablePositionLevel: {
            type: 'enum',
            enum: ['true', 'false'],
            default: 'false',
            comment: 'Desabilita nível de cargo',
        },
        disableSchoolLevel: {
            type: 'enum',
            enum: ['true', 'false'],
            default: 'false',
            comment: 'Desabilita nível escolar',
        },
        createdAt: {
            type: 'timestamp',
            default: () => 'CURRENT_TIMESTAMP',
            nullable: false,
            precision: null as any,
            createDate: true,
            comment: 'Data de Criação',
        },
        updatedAt: {
            type: 'timestamp',
            default: () => 'CURRENT_TIMESTAMP',
            nullable: false,
            precision: null as any,
            updateDate: true,
            comment: 'Data de Atualização',
            onUpdate: 'CURRENT_TIMESTAMP',
        },
    },
    relations: {
        company: {
            type: 'one-to-one',
            target: 'company',
            joinColumn: {
                name: 'idCompany',
                referencedColumnName: 'idCompany',
                foreignKeyConstraintName: 'FK_Company_CompanySettings',
            },
            onUpdate: 'CASCADE',
            onDelete: 'CASCADE',
        },
        companySettingsTranslations: {
            type: 'one-to-many',
            target: 'companySettingsTranslation',
            inverseSide: 'companySettings',
        },
    },
    indices: [{ columns: ['idCompany'], name: 'FK_Company_RadarDescription' }],
    uniques: [{ columns: ['idCompany'], name: 'idCompany' }],
});

当前迁移生成的问题SQL #

迁移后会创建两个重复的唯一索引：

CREATE TABLE `companySettings` (
                `idCompanySettings` smallint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Chave primária',
                `idCompany` smallint UNSIGNED NOT NULL COMMENT 'Chave da Empresa',
                `radarCulturalImage` varchar(100) CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci" NULL COMMENT 'Imagem customizada da empresa usada no radar' DEFAULT NULL,
                `disableCountry` enum ('true', 'false') NOT NULL COMMENT 'Desabilita o país' DEFAULT 'false',
                `disablePositionLevel` enum ('true', 'false') NOT NULL COMMENT 'Desabilita nível de cargo' DEFAULT 'false',
                `disableSchoolLevel` enum ('true', 'false') NOT NULL COMMENT 'Desabilita nível escolar' DEFAULT 'false',
                `createdAt` timestamp NOT NULL COMMENT 'Data de Criação' DEFAULT CURRENT_TIMESTAMP,
                `updatedAt` timestamp NOT NULL COMMENT 'Data de Atualização' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX `FK_Company_RadarDescription` (`idCompany`),
                UNIQUE INDEX `idCompany` (`idCompany`),
                UNIQUE INDEX `REL_d2f2efb4db98cf928a75c79447` (`idCompany`),
                PRIMARY KEY (`idCompanySettings`)
            ) ENGINE = InnoDB

之前尝试设置createForeignKeyConstraints: true，虽然只会生成一个唯一索引，但外键约束也会消失，不符合需求。

解决方案 #

1. 删除手动定义的重复唯一索引配置 #

TypeORM会为一对一关系的关联字段自动生成唯一约束，你手动在uniques数组中添加的配置会导致重复创建索引，所以先移除实体中的uniques配置：

// 移除这一行
uniques: [{ columns: ['idCompany'], name: 'idCompany' }],

2. 在joinColumn中指定唯一索引名称 #

TypeORM允许在joinColumn配置里通过uniqueConstraintName参数自定义自动生成的唯一索引名称，修改company关系的配置：

company: {
    type: 'one-to-one',
    target: 'company',
    joinColumn: {
        name: 'idCompany',
        referencedColumnName: 'idCompany',
        foreignKeyConstraintName: 'FK_Company_CompanySettings',
        uniqueConstraintName: 'UQ_CompanySettings_IdCompany' // 自定义唯一索引名称
    },
    onUpdate: 'CASCADE',
    onDelete: 'CASCADE',
},

3. 保留外键约束 #

不需要修改createForeignKeyConstraints参数（默认值为true），这样TypeORM会同时生成：

• 你自定义名称的唯一索引
• 指定名称的外键约束

修改后的迁移效果 #

生成的SQL会只包含一个自定义名称的唯一索引，同时保留外键约束：

CREATE TABLE `companySettings` (
    `idCompanySettings` smallint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Chave primária',
    `idCompany` smallint UNSIGNED NOT NULL COMMENT 'Chave da Empresa',
    `radarCulturalImage` varchar(100) CHARACTER SET "utf8mb4" COLLATE "utf8mb4_unicode_ci" NULL COMMENT 'Imagem customizada da empresa usada no radar' DEFAULT NULL,
    `disableCountry` enum ('true', 'false') NOT NULL COMMENT 'Desabilita o país' DEFAULT 'false',
    `disablePositionLevel` enum ('true', 'false') NOT NULL COMMENT 'Desabilita nível de cargo' DEFAULT 'false',
    `disableSchoolLevel` enum ('true', 'false') NOT NULL COMMENT 'Desabilita nível escolar' DEFAULT 'false',
    `createdAt` timestamp NOT NULL COMMENT 'Data de Criação' DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` timestamp NOT NULL COMMENT 'Data de Atualização' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `FK_Company_RadarDescription` (`idCompany`),
    UNIQUE INDEX `UQ_CompanySettings_IdCompany` (`idCompany`),
    PRIMARY KEY (`idCompanySettings`),
    CONSTRAINT `FK_Company_CompanySettings` FOREIGN KEY (`idCompany`) REFERENCES `company` (`idCompany`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB

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