Query super slow when foreign key is set to no duplicates

[Moss100]

I'm a bit puzzled.

I have a table called [CARS] which has primary key called CarID (autonumber)

I have a table called [CarExtended] with a field to link to [CARS] called CarExt_CarID_Link (set as long int)

I have created a query between the two. This all works fine, but I then wanted to set an index on the field CarExt_CarID_Link to prevent duplicates.

Without the index the query runs as expected (in around 0.5 second). With the index set, the query then takes around 12 seconds.

Can anyone help explain why - or what is causing this?

Thanks Mark

 

[CreativeStrat]

Hi Mark

Reading the post the best way to get help on this is show us your tables and the query. Ideally show us the tables in design view and the query in SQL and we should then be positioned to best help.

I suspect from the description the underlying structure is the issue.

Hope that helps.