every time I try to build the database I keep getting error object name already exists for code. please bare in mind that code is a unique key not the primary key not sure if that may affect this.
Here is the source code I solved first issue it was a mistake on my end though what I need is to reference a unique key (CODE) rather than the primary key (ID) how can I achieve this through Cuba studio without having to alter the code through IDE.
create table CPS_ITEM (
ID int generated by default as identity(start with 1) not null,
VERSION integer not null,
CREATE_TS timestamp,
CREATED_BY varchar(50),
UPDATE_TS timestamp,
UPDATED_BY varchar(50),
DELETE_TS timestamp,
DELETED_BY varchar(50),
--
SUPPLIER_NUMBER_ID integer,
CODE varchar(255) not null,
NAME_ENGLISH varchar(255),
NAME_ARABIC varchar(255),
BRAND_ID integer,
CATEGORY_ID integer,
COUNTRY_ID integer,
CURRENCY_ID integer,
GROUP_ID integer,
SUBGROUP_ID integer,
LENGTH decimal(19, 2),
WIDTH decimal(19, 2),
HEIGHT decimal(19, 2),
--
primary key (ID)
)^
-- constraints
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_SUPPLIER_NUMBER foreign key (SUPPLIER_NUMBER_ID) references CPS_ITEM(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_BRAND foreign key (BRAND_ID) references CPS_BRAND(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_CATEGORY foreign key (CATEGORY_ID) references CPS_ITEM_CATEGORY(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_COUNTRY foreign key (COUNTRY_ID) references CPS_COUNTRY(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_CURRENCY foreign key (CURRENCY_ID) references CPS_CURRENCY(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_GROUP foreign key (GROUP_ID) references CPS_GROUP(ID)^
alter table CPS_ITEM add constraint FK_CPS_ITEM_ON_SUBGROUP foreign key (SUBGROUP_ID) references CPS_SUB_GROUP(ID)
create table CPS_ITEM_SUPPLIER_NUMBER (
ID int generated by default as identity(start with 1) not null,
--
ITEM_ID integer,
SUPPLIER_ID integer,
--
primary key (ID)
)^
-- constraints
alter table CPS_ITEM_SUPPLIER_NUMBER add constraint FK_CPS_ITEM_SUPPLIER_NUMBER_ON_ITEM foreign key (ITEM_ID) references CPS_ITEM(ID)^
alter table CPS_ITEM_SUPPLIER_NUMBER add constraint FK_CPS_ITEM_SUPPLIER_NUMBER_ON_SUPPLIER foreign key (SUPPLIER_ID) references CPS_ITEM(ID)^