I am using spring data jpa for creation of my tables. My Requirement.
I have two Tables. Basket Table - It has one to many Relationship with the Item table. A basket can have many Items.
Item Table - Here an Item can be associated with many Baskets.
I am using IGNORE_ROW_ON_DUPKEY_INDEX to make sure that the same combination of basketId and itemId, does not get persisted
So there is a mapping table in between in which holds the mapping of baskets and items. Now, in this table, i want the combination of basketId and itemId to be unique. Below is my entity stucture.
@Entity
Class Basket{
@Id
private long basketId;
...
@OneToMany(cascade = {CascadeType.Merge, CascadeType.DETACH, CascadeType.REFRESH})
@JoinTable(
name= "mapping_table",
joinColumns = @JoinColumn(name ='basketId'),
inverseJoinColumns = @JoinColumn(name ='itemId'),
indexes = {@Index(name = "my_index", columnList = "basketId, itemId", unique = true)}
@SQLInsert(sql = "INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX (mapping_table, my_index) */ INTO mapping_table(basketId, itemId) values (?,?)")
private List<Item> itemList;
...
}
@Entity
Class Item{
@Id
private long itemId;
}
my_index with the combination of both the keys are getting created, as expected in the mapping_table
Problem 1: In the mapping_table, for some wierd reason, a new unique constraint with only itemId is created. Due to this unique key constraint, i am not able to persist rows where an item is associated with multiple baskets. As i said, i want the combination of both the keys to be unique and i am achieving this by creating the index (my_index)
Problem 2: Why is basketId (which is also Identifier) in the basketTable not marked as unique in the mapping table. This not a problem but more of a question. Becuase itemId which is identifier in the item table has unique key constraint in the mapping table.
Solution : I create the tables using spring data jpa, login to the db manually and drop the unique key constraint and the persist. But this is not possible in Production
Question
I want to do alter table to drop the constraint first before the persist thing happens. How to do that.
P.S, As you can see, I have imagined these tables and have not put the names of the actual table. Not withstanding the made up table names, the problem statement is accurate. I am using Oracle as target DB
