Hi
When Studio generate indices for foreign key constraints, it uses following name pattern : IDX_<table_name>_<field_name_without_trailing_id>.
In some cases where using compound names like in the code below, this may generate the same index name twice. In this particular case the problem can be solved by renaming MODE_ID to DELIVERY_MODE_ID in the corresponding table, but this could use a more robust solution for complex models.
I would suggest more explicit separation in the index name between the table name and the field name, for instance _ON _.
Using this solution, Studio would have generated two distincts names : IDX_CUSTOMER_ORDER_ON_DELIVERY_MODE and IDX_CUSTOMER_ORDER_DELIVERY_ON_MODE.
create table APP_DELIVERY_MODE (
ID uuid,
}
create table APP_CUSTOMER_ORDER (
ID uuid,
DELIVERY_MODE_ID uuid,
)^
create table APP_CUSTOMER_ORDER_DELIVERY (
ID uuid,
MODE_ID uuid,
--
primary key (ID)
)^
alter table APP_CUSTOMER_ORDER add constraint FK_APP_CUSTOMER_ORDER_DELIVERY_MODE foreign key (DELIVERY_MODE_ID) references APP_DELIVERY_MODE(ID)^
create index **IDX_APP_CUSTOMER_ORDER_DELIVERY_MODE** on APP_CUSTOMER_ORDER (DELIVERY_MODE_ID)^
alter table APP_CUSTOMER_ORDER_DELIVERY add constraint FK_APP_CUSTOMER_ORDER_DELIVERY_MODE foreign key (MODE_ID) references APP_DELIVERY_MODE(ID)^
create index **IDX_APP_CUSTOMER_ORDER_DELIVERY_MODE** on APP_CUSTOMER_ORDER_DELIVERY (MODE_ID)^
Mike