Error Invalid Index...

[JaedenRuiner]

Okay,

This Should be simple but something is not right:

1. Create Connection via SQL CE
2. Create DB file
3. Add Tables

Tables:

1. tblLocation
   • Loc_ID, nvarchar(6), primary
2. tblMaterial
   • Mat_ID, nvarchar(6), primary
3. tblEmail
   • Email_ID, nvarchar(10), primary
   • BOL, nvarchar(10), primary
   • Location, nvarchar(6), not null
4. tblBOL
   • BOL_ID, nvarchar(10), primary
   • Material, nvarchar(6), primary
   • Location, nvarchar(6), not null
   • quantity, numeric(10)

Added Index (other than the automatic PK indexes):

1. tblEmail - IDX_tblEmail
   • BOL,Location UNIQUE

Relations:

1. tblEmail
   • FK_LocationEmail
     tblLocation.Loc_ID -> tblEmail.Location
2. tblBOL
   • FK_Material
     • tblMaterial.Mat_ID -> tblBOL.Material
   • FK_Email
     • tblEmail.BOL -> tblBOL.BOL_ID
     • tblEmail.Location -> tblBOL.Location

Now with all this made and set up the tables, keys, indexes and relations are all appropriate and solid, and the way I want them, but when I add that final FK_Email key, linking tblEmail as the Primary Key holder and tblBOL as the Foreign Key holder via the unique index of BOL & Location in tblEmail, I proceed to the Manage Relations view in the Table Properties and Get this Error:

Now this is just confusing as all get out. What index error?!?! What could possibly be screwing up a simple database relation of a Unique Index in a one to many relationship to another table where the two columns in question are not unique?

Please help,
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[cjard]

wayoftheleaf requires a password to see the image


So, tell me, about tblEmail.. if BOL and Location are sufficient to uniquely id a record, why are they not the PK?
Whatever a BOL is, there is only one of them per location, and that only one email will ever be sent from that location?