Hello,

Seems like this should work. I have a list of contacts. Contacts can have
multiple addresses of several types (billing, shipping), including former
addresses. To determine the current or primary address of each type

Contacts
AddressTypeRef
Addresses
ConAddressesPrm (ie primary addresses)

this script sequence produces an error:
unsuccessful metadata update, could not find UNIQUE INDEX with specified
columns

Is there something wrong with my thinking?

Thanks, Kyle


create table ConAddressesPrm
(PrmId integer not null
,ConId integer not null
,TypeId integer not null
,AddrId integer not null

,constraint pkConAddressesPrm_PrmId primary key (PrmId)
,constraint ukConAddressesPrm_Combo unique (ConId, TypeId)
);
commit;

/* this is pk in foreign table */
alter table ConAddressesPrm add constraint fkConAddressesPrm_AddrId
foreign key (AddrId)
references ConAddresses (AddrId);
commit;

/* this fk in foreign table */
alter table ConAddressesPrm add constraint fkConAddressesPrm_ConId
foreign key (ConId)
references ConAddresses (ConId)
on update cascade on delete cascade;
commit;

/* this is pk in foreign table */
alter table ConAddressesPrm add constraint fkConAddressesPrm_TypeId
foreign key (TypeId)
references ConAddrTypeRef (TypeId)
commit;