System.Data.SqlClient.SqlException – Violation of UNIQUE KEY constraint

The error System.Data.SqlClient.SqlException – Violation of UNIQUE KEY constraint occurs when you attempt to insert or update a record in a SQL Server table, but the operation violates a UNIQUE constraint defined on one or more columns. This means the value you’re trying to insert or update already exists in the table, and duplicates are not allowed for that column or combination of columns.

Common Causes

1. Duplicate Data:

• You’re trying to insert a record with a value that already exists in a column with a UNIQUE constraint.
• Example: Inserting a duplicate email address in a column marked as UNIQUE.

1. Unique Key Constraint:

• A UNIQUE constraint is explicitly defined on one or more columns in the table.
• Example: A primary key or a unique index enforces uniqueness.

1. Case Sensitivity or Collation:

• SQL Server’s collation settings may treat strings as identical even if they differ in case (e.g., ABC and abc may be considered duplicates depending on the collation).

1. Concurrency Issues:

• In multi-user environments, two users might try to insert the same data simultaneously, causing a conflict.

---

Solutions

1. Check for Existing Data

Before inserting or updating, check if the value already exists in the table.

Example:

   IF NOT EXISTS (SELECT 1 FROM TableName WHERE UniqueColumn = @Value)
   BEGIN
       INSERT INTO TableName (UniqueColumn) VALUES (@Value);
   END
   ELSE
   BEGIN
       PRINT 'Duplicate value found. Insert aborted.';
   END

In C#:

   using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM TableName WHERE UniqueColumn = @Value", connection))
   {
       cmd.Parameters.AddWithValue("@Value", value);
       int count = (int)cmd.ExecuteScalar();
       if (count == 0)
       {
           // Insert the record
       }
       else
       {
           Console.WriteLine("Duplicate value found. Insert aborted.");
       }
   }

2. Handle Duplicates Gracefully

Use TRY...CATCH in SQL Server to handle the error gracefully.

Example:

   BEGIN TRY
       INSERT INTO TableName (UniqueColumn) VALUES (@Value);
   END TRY
   BEGIN CATCH
       PRINT 'Error: ' + ERROR_MESSAGE();
   END CATCH

In C#:

   try
   {
       using (SqlCommand cmd = new SqlCommand("INSERT INTO TableName (UniqueColumn) VALUES (@Value)", connection))
       {
           cmd.Parameters.AddWithValue("@Value", value);
           cmd.ExecuteNonQuery();
       }
   }
   catch (SqlException ex)
   {
       if (ex.Number == 2627) // Unique key violation error code
       {
           Console.WriteLine("Duplicate value found. Insert aborted.");
       }
       else
       {
           throw; // Re-throw other exceptions
       }
   }

3. Review the Unique Constraint

• Identify the column(s) with the UNIQUE constraint.
• Ensure the data being inserted or updated does not conflict with existing data. Example:

   -- Check unique constraints on a table
   SELECT 
       COLUMN_NAME, 
       CONSTRAINT_NAME 
   FROM 
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
   WHERE 
       TABLE_NAME = 'TableName';

4. Use MERGE Statement

The MERGE statement allows you to handle inserts and updates in a single operation, avoiding duplicates.

Example:

   MERGE INTO TableName AS target
   USING (SELECT @Value AS UniqueColumn) AS source
   ON target.UniqueColumn = source.UniqueColumn
   WHEN NOT MATCHED THEN
       INSERT (UniqueColumn) VALUES (source.UniqueColumn);

5. Modify the Data

If the duplicate value is not critical, you can modify the data to make it unique (e.g., appending a timestamp or GUID).

Example:

   string uniqueValue = originalValue + "_" + Guid.NewGuid().ToString("N");

6. Remove or Modify the Constraint

If the UNIQUE constraint is no longer needed, you can drop it.

Example:

   ALTER TABLE TableName DROP CONSTRAINT ConstraintName;

Note: Be cautious when removing constraints, as they are often in place to enforce data integrity.

---

Debugging Steps

1. Identify the Constraint:

• Check which column(s) have the UNIQUE constraint.
• Use the following query to find constraints:
  sql SELECT COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName';

1. Check Existing Data:

• Query the table to see if the value already exists.
  sql SELECT * FROM TableName WHERE UniqueColumn = @Value;

1. Review Application Logic:

• Ensure the application is not sending duplicate values unintentionally.

1. Check Collation:

• Verify the collation of the column to ensure case sensitivity is handled correctly.
  sql SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');

---

Example Scenario

Suppose you have a table Users with a UNIQUE constraint on the Email column:

CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY,
    Email NVARCHAR(255) UNIQUE,
    Name NVARCHAR(255)
);

If you try to insert a duplicate email, you’ll get the error. To handle this:

BEGIN TRY
    INSERT INTO Users (Email, Name) VALUES ('test@example.com', 'John Doe');
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH

In C#:

try
{
    using (SqlCommand cmd = new SqlCommand("INSERT INTO Users (Email, Name) VALUES (@Email, @Name)", connection))
    {
        cmd.Parameters.AddWithValue("@Email", "test@example.com");
        cmd.Parameters.AddWithValue("@Name", "John Doe");
        cmd.ExecuteNonQuery();
    }
}
catch (SqlException ex) when (ex.Number == 2627)
{
    Console.WriteLine("Duplicate email found. Insert aborted.");
}

By following these steps, you can resolve the Violation of UNIQUE KEY constraint error and ensure data integrity in your application.