CODEWITHSUNDEEP
sqlsql-server
Mayou
Mayou

Reputation: 8818

SQL Server : insert record if not duplicate over certain columns

I have very limited experience with SQL, so I am struggling to ask the right question.

Imagine I have a table with 5 columns

Simulation_ID   Param_1   Param_2   Param_3   Param_4  Param_5  Output_folder

This table summarizes simulations that are run using certain parameters, and the output folder of the result.

I would like to prevent duplicates from being inserted in this table. In my case, a simulation must have a unique set of parameters. Therefore, no simulation should have the same combination of parameters. I was tempted to define a primary key as (ID, Param_1, Param_2, Param_3, Param_4, Param_5), but I read that composite keys are not recommended due to poor performance. Currently, Simulation_ID is the primary key of the table.

Imagine if someone wants to add a simulation that has a unique ID, but the same set of parameters that was used by another existing record.

Example:

Current data in the table:

Simulation_ID   Param_1   Param_2   Param_3   Param_4  Param_5  Output_folder
-----------------------------------------------------------------------------
1               0.5       0.4       0.3       0.2      0.1      folder1
2               10        0.4       0.3       0.2      0.1      folder2

And the new record is:

Simulation_ID   Param_1   Param_2   Param_3   Param_4  Param_5  Output_folder
-----------------------------------------------------------------------------
3               0.5       0.4       0.3       0.2      0.1      folder3

Technically, this record, although having a different ID and output_folder, has the exact same values (Param_1 to Param_5) as record #1. I would like to do a check where if this happens, an error is thrown and the record isn't inserted.

Is that possible? In reality, my table has over 20 columns, and 17 out of 20 columns is what I would like to use to flag and prevent duplicates.

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want a unique constraint or (equivalently) a unique index. You would add this as:

alter table t add constraint unq_t_params
    unique (param_1, param_2, param_3, param_4, param_5);

If you attempt to add a set of parameters that are already in the table, this will return an error, preventing the insertion from happening.

Upvotes: 2

Related Questions