Hi need some guidance and help on MariaDB with Hibernate,I have many to many mapping between two different tables but while starting server hibernate generates Primary key Instead Of Unique Key don't know why ? is there any dialect problem? Entity is like this.

@ManyToMany(fetch = FetchType.LAZY,cascade=CascadeType.REFRESH)
@JoinTable(name = "step_childpage", 
joinColumns = @JoinColumn(name = "step_id"), 
inverseJoinColumns = @JoinColumn(name = "childpage_id"),
uniqueConstraints= {@UniqueConstraint(name="UK_step_childpage",columnNames= {"childpage_id","step_id"})},
[email protected](name="FK_step_childpage_step_id"),
[email protected](name="FK_step_childpage_page_id")
)
public Set<BotPage> getChildPages() {
    return childPages;
}
public void setChildPages(Set<BotPage> childPages) {
    this.childPages = childPages;
}

and though i have manually written table hibernate alters while server start-up. modified table is like this.

CREATE TABLE `page_childstep` (
    `page_id` BIGINT(20) NOT NULL,
    `childstep_id` BIGINT(20) NOT NULL,
    PRIMARY KEY (`childstep_id`, `page_id`),
    INDEX `FK_page_childstep_page_id` (`page_id`),
    CONSTRAINT `FK_page_childstep_page_id` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`),
    CONSTRAINT `FK_page_childstep_step_id` FOREIGN KEY (`childstep_id`) REFERENCES `teststep` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

1 Answers

0
Rick James On

A PRIMARY KEY is a UNIQUE key.

The indexes in page_childstep are optimal.

  • The PK allows for efficiently getting from a childstep to a page(s).
  • The INDEX allows for efficiently getting from a page to a childstep(s).

Note: In InnoDB, an INDEX implicitly includes the columns of the PK. So INDEX(page_id) is effectively a BTree ordered by (page_id, childstep_id).

(BIGINT is gross overkill; FKs cost some effort. But these are other topics.)