Multiple Foreign Keys Point to Same Table

Hi,

I was wondering how can I make a table with 2 foreign keys that point to the same table.

So I have items table and I am trying to create supplier items table

items:
id,
code,
name,

supplierItems:
id,
item -> item.code,
supplier -> item.code

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.

Thanks

Hi,

Please provide the source code of your entities and full text of the error.

Hi,

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)^

Studio supports creation of references only to primary keys. You have to code your relationship manually.