At 07:12 AM 5/02/2007, you wrote:

>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?

The message is misleading (and, yes, many have complained about
it!) In fact, you can't form a foreign key to anything but the
primary key or a unique constraint in the parent table. More inline...

>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;

AddrId will need to be the PK or a unique constraint in ConAddresses.

>/* 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;

ConID will have to be the PK or a unique constraint in ConAddresses.

Well, both AddrID and ConID can't be the PK and the error message is
telling you that you don't have unique constraints on AddrID and
ConID individually. If (ConID,AddrID) is the PK (or a unique
constraint) in ConAddresses then your FK needs to be the same
columns, same order, i.e.
alter table ConAddressesPrm add constraint fkConAddressesPrm
foreign key (ConId,AddrID)
references ConAddresses (ConID,AddrID)
on update cascade on delete cascade;

The cascade applies to the child-to-parent relationship: if the
record in ConAddresses with the matching PK is deleted, then any
matching children in ConAddressesPrm will disappear too.

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

TypeId will have to be the PK or a unique constraint in ConAddrTypeRef

./heLen