在JHipster中创建双字段复合主键时SQL生成异常的问题

学习JHipster时，尝试创建一张由两个字段组合成主键的自关联多对多关联表。Liquibase变更日志配置看似没问题，但执行./mvnw -Pdev命令时，系统生成的SQL错误地仅将单个字段设为主键。

JDL 配置 #

entity Category {
  name String unique
  createdBy String
  createdDate Instant
  lastModifiedBy String
  lastModifiedDate Instant
}
   
relationship ManyToMany {
  Category{parent} to Category
}

Liquibase 变更日志：added_entity_Category.xml #

<changeSet id="20240607105313-1" author="jhipster">
    <createTable tableName="category">
        <column name="id" type="bigint">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(255)">
            <constraints nullable="true" unique="true" uniqueConstraintName="ux_category__name" />
        </column>
        <column name="created_by" type="varchar(255)">
            <constraints nullable="true" />
        </column>
        <column name="created_date" type="${datetimeType}">
            <constraints nullable="true" />
        </column>
        <column name="last_modified_by" type="varchar(255)">
            <constraints nullable="true" />
        </column>
        <column name="last_modified_date" type="${datetimeType}">
            <constraints nullable="true" />
        </column>
        <!-- jhipster-needle-liquibase-add-column - JHipster will add columns here -->
    </createTable>
    <dropDefaultValue tableName="category" columnName="created_date" columnDataType="${datetimeType}"/>
    <dropDefaultValue tableName="category" columnName="last_modified_date" columnDataType="${datetimeType}"/>
</changeSet>

<changeSet id="20240607105313-1-relations" author="jhipster">
    <createTable tableName="rel_category__parent">
        <column name="parent_id" type="bigint">
            <constraints nullable="false"/>
        </column>
        <column name="category_id" type="bigint">
            <constraints nullable="false"/>
        </column>
    </createTable>

    <addPrimaryKey columnNames="category_id, parent_id" tableName="rel_category__parent"/>
</changeSet>

Liquibase 约束配置：added_entity_constraints_Category.xml #

<addForeignKeyConstraint baseColumnNames="category_id"
                         baseTableName="rel_category__parent"
                         constraintName="fk_rel_category__parent__category_id"
                         referencedColumnNames="id"
                         referencedTableName="category"
                         />

<addForeignKeyConstraint baseColumnNames="parent_id"
                         baseTableName="rel_category__parent"
                         constraintName="fk_rel_category__parent__parent_id"
                         referencedColumnNames="id"
                         referencedTableName="category"
                         />

生成的错误SQL #

CREATE TABLE public.rel_category__parent (parent_id bigint NOT NULL, category_id bigint NOT NULL);
ALTER TABLE
  public.rel_category__parent
ADD
  CONSTRAINT rel_category__parent_pkey PRIMARY KEY (category_id)

解决方法 #

• 调整Liquibase主键定义方式：把组合主键的定义直接放到createTable的列约束中，替换单独的addPrimaryKey标签，避免执行顺序或解析问题：

  <createTable tableName="rel_category__parent">
      <column name="parent_id" type="bigint">
          <constraints nullable="false" primaryKey="true" primaryKeyName="rel_category__parent_pkey"/>
      </column>
      <column name="category_id" type="bigint">
          <constraints nullable="false" primaryKey="true" primaryKeyName="rel_category__parent_pkey"/>
      </column>
  </createTable>
  

• 重新生成实体代码：删除现有Liquibase变更文件，重新执行jhipster entity Category命令，确保JHipster正确解析多对多自关联的主键配置。
• 检查数据库方言配置：确认application-dev.yml中的数据库方言设置正确，不同数据库对组合主键的语法支持可能存在差异。

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