Solved Avoid Duplicates Of Multiple Fields

[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.

 

[CJ_London]

not sure how many fields you can have in a composite unique index (certainly more than 2) but what I have done in the past is hash all the relevant fields and store the hashed value in a unique indexed field

 

[Imb]

I'll go composite index route.

Hi Dalski,

I don't use composite keys.
When I am in my "new record"-form, and the user wants to store it, a couple of checks are done.
One is are all obligated controls. If not, no save, and return to the "new record"-form. The same holds for all kind of other conditions (too many characters, invalid values, out-of-range conditions, etc.
Finally a check is done on those combination of control values that must be unique. For that purpose a simple query is generated that checks if such a record already exists. If it exists, again, no save, and return to the "new record"-form, including tha appropriate messages.
When the record does not yet exists, the records is created, and the "new record"-form is closed.

The same holds for the "edit-record"-form, to update fields.

BTW: the "new-record"-form and "edit-record"-form are generic, that is it is used for any record in any application.

 

[GPGeorge]

We're not talking about composite keys, we're talking about a unique index on two fields. And When a unique index is created for the fields in this table, the database engine does all of the work described in your second paragraph, except for converting the database engine's error message to a user friendly error message.

The most important differences, according to my AI assistant:

They're similar but have some important differences:

Composite Primary Key:

• Enforces uniqueness across multiple columns
• Does NOT allow NULLs in any of the key columns
• A table can only have one primary key (composite or otherwise)
• Creates a unique clustered index by default (unless you specify otherwise)
• Has semantic meaning - it's THE identifier for the row
• Commonly referenced by foreign keys

Unique Index on Multiple Fields:

• Enforces uniqueness across multiple columns
• CAN allow NULLs (in SQL Server, you can have multiple rows with NULL values in a unique index, though there are nuances)
• A table can have multiple unique indexes
• Can be clustered or non-clustered
• More of an implementation detail to enforce a business rule