unique key Error.

[finnbell]

JPA

@entity
@getter
@Setter
@NoArgsConstructor
public class MenuEntity implements Serializable {

@Id
@GeneratedValue()
private Long id;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="parent_id")
private MenuEntity parent;

private String name;

@Column( nullable = false )
private int listOrder;

@Column( nullable = false , **unique = true**)
private String menuCode;

@OneToMany(mappedBy = "menuEntity")
private List<MenuItemEntity> menuItemList = new ArrayList<>();

....
}

P6spy SQL query excute ->
alter table menu_entity add constraint UK_lk5c2lk5kyawukpw7lvotbivg unique (menu_code);

Error ->
Error executing DDL "alter table menu_entity add constraint UK_lk5c2lk5kyawukpw7lvotbivg unique (menu_code)"

I know that, SQLite does not support "alter table ~~~ add constraint " ,
SQlite query should be " CREATE INDEX ~~~~ "

Also, I tried @table ( constraint ~~ ) , It was same problem.

It may be different Sqlite from Other SQL query.
So, do you fix that problem on Dialect ?

Thanks in advance!

[gwenn]

Could you please check hibernate.hbm2ddl.auto properties (or something similar) ?

      <!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
      <!--property name="hibernate.hbm2ddl.auto" value="validate"/-->
      <property name="hibernate.hbm2ddl.auto" value="none"/>

Since SQLite doesn't fully support ALTER TABLE, you cannot use update (you must upgrade the DB schema yourself).

[finnbell]

spring.jpa.hibernate.ddl-auto=update

I see. I've just excute native query by myself.

Thanks.

[zengjian86]

fixed

     /**
       * SQLite use table creation sql to define unique constraints.
       */
      @Override
      public String getColumnDefinitionUniquenessFragment(Column column, SqlStringGenerationContext context) {
          return "";
      }

      /**
       * SQLite do not support 'alter table' to add constraints.
       */
      @Override
      public String getAlterTableToAddUniqueKeyCommand(UniqueKey uniqueKey, Metadata metadata, SqlStringGenerationContext context) {
          return "";
      }

      /**
       * SQLite uses table creation sql to define unique constraints, and do not support alter table sql to add
       * constraints.
       * Such as "create table person( first_name varchar(255),last_name varchar(255),unique(first_name, last_name) )".
       */
      @Override
      public String getTableCreationUniqueConstraintsFragment(Table table, SqlStringGenerationContext context) {
          // get all uniqueKeys
          StringBuilder builder = new StringBuilder();
          Iterator<UniqueKey> iter = table.getUniqueKeyIterator();
          while (iter.hasNext()) {
              UniqueKey key = iter.next();
              builder.append(", ").append(uniqueConstraintSql(key));
          }
          return builder.toString();
      }


      /**
       * SQLite do not support 'drop constraint'.
       */
      public String getAlterTableToDropUniqueKeyCommand(UniqueKey uniqueKey, Metadata metadata,
                                                        SqlStringGenerationContext context) {
          return "";
      }

[finnbell]

Where can I find SqlStringGenerationContext ?