Solved Avoid Duplicates Of Multiple Fields (3 Viewers)

[dalski]

In Access I used a composite index of 2 fields, but unsure best option in MSSQL for performance to avoid a duplicate record of the combination of the 2 fields?

In the child table (relevant to topic) I have a foreign-key & a normal field.

I think I have 2 options?

• Composite Index like in Access? Should I be concerned about Composite Index Fragmentation? Also creating an unnecessary index on a field.
• Trigger on creating the record to test if they exist & avoid creating the record if it does. I'm already going to have triggers so probably best to avoid this & simplify I imagine.

Any recommendations?

 

[GPGeorge]

I would implement the composite index. It's not an unnecessary index, is it? It serves a purpose, so your question really would be, is it necessary to ensure duplicate records don't exist or not.

I can't speak to the idea of using a trigger in the context you describe. Either way, you can't create a record when doing so would violate the unique index constraint. I suppose you mean, "attempt to create the record", though. Again, the database will reject the duplicates anyway if there's a unique index on those fields.

By the way, you don't have to create a separate index on a field designated as a foreign key in SQL Server. You could have only the unique composite index.

 

[dalski]

Thanks George, much appreciated, I'll go composite index route.

 

[Minty]

Be aware that you won't get an elegant capturable error message from Access about the index violation.
You'll have to check in the form beforehand for duplication via VBA on a SQL Linked table.