Unique index multiple-key issue...

[cheuschober]

Okay, anyone have an idea what's happening here?

It seems like it -should- work to me but it's not which leaves me to wonder where I'm missing something.

Any help'd be appreciated.

Thanks,
~Chad

 

[The_Doc_Man]

Take a good look at the lines joining the fields you have specified. Are these relationships really one-to-one? 'cause when you see the lines with the little black circles on the end, that is what you are saying. 'tain't one-to-many.

I'm seeing an inconsistency that I don't think should be there.

For instance, look at the OTHER relationship from the same table, the one in which only the country ID is involved. It is clearly a many-to-one mapping. See the difference in graphic representation?

Perhaps you've got the one and many sides reversed in trying to set up the relationship? If the prime key of your table is actually the country id and postal code, you should have a single index that consists of only those two fields and it should be marked as "no dups."

 

[cheuschober]

You know I initially thought I might've switched up the relationship too but I didn't. As for the relationship being shown, the error only shows up because I can't enforce RI as I should be able to do.

Basically the issue is how does one set up a one-to-many relationship on a multiple key field?

Since the PK of my primary table is both an ISO Country code and a PostalCode that creates its own unique index (the pk index). So why, then, am I being told that I don't have a unique index when I attempt to enforce RI in my foreign table? Were I to attempt to use each field individually, combinations of CountryCode and PostalCode that don't exist in my primary lookup table could make it into the database and that would, well, it would just be bad.

So any more thoughts to be had?

Regards,
~Chad

 

[Pat Hartman]

It looks OK to me. Try swapping the columns so they are physically in the same order in both tables. If that doesn't work, try removing the relationship entirely and rebuilding the pk on the 1-side table. Compact the db and then try the relationship again. It is possible that the index is corrupt.

 

[cheuschober]

It looks OK to me. Try swapping the columns so they are physically in the same order in both tables. If that doesn't work, try removing the relationship entirely and rebuilding the pk on the 1-side table. Compact the db and then try the relationship again. It is possible that the index is corrupt.

Did as you suggested and cleaned it right up. Thanks Pat!
~Chad

 

[Pat Hartman]

Sometimes software is just too picky for words