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

The System.Data.SqlClient.SqlException – Violation of PRIMARY KEY constraint is a runtime exception in C# that occurs when the application tries to insert or update a row in a SQL Server database, but the operation violates the primary key constraint. This typically happens when:

1. The primary key value already exists in the table (duplicate key).
2. The primary key value is NULL (if the column does not allow NULL values).
3. The primary key constraint is not properly enforced in the application.

Here’s how you can troubleshoot and fix this issue:

---

1. Check for Duplicate Primary Key Values

• Ensure that the primary key value being inserted or updated does not already exist in the table. Example:

   var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (1, 'John')", connection);
   command.ExecuteNonQuery(); // Error: Duplicate primary key if 'Id = 1' already exists

Fix:

   var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (2, 'Jane')", connection); // Use a unique primary key
   command.ExecuteNonQuery();

---

2. Check for NULL Primary Key Values

• Ensure that the primary key value is not NULL if the column does not allow NULL values. Example:

   var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (NULL, 'John')", connection); // Error: Primary key cannot be NULL

Fix:

   var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (1, 'John')", connection); // Provide a non-NULL primary key
   command.ExecuteNonQuery();

---

3. Use Auto-Incrementing Primary Keys

• Use an auto-incrementing primary key (identity column) to avoid duplicate key issues. Example:

   CREATE TABLE MyTable (
       Id INT PRIMARY KEY IDENTITY(1,1), -- Auto-incrementing primary key
       Name VARCHAR(50)
   );

---

4. Check Database Schema

• Ensure that the primary key constraint is properly defined in the database schema. Example:

   ALTER TABLE MyTable
   ADD CONSTRAINT PK_MyTable PRIMARY KEY (Id); -- Define primary key constraint

---

5. Use try-catch for Error Handling

• Use a try-catch block to handle the SqlException gracefully and log detailed error information. Example:

   try
   {
       var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (1, 'John')", connection);
       command.ExecuteNonQuery();
   }
   catch (SqlException ex)
   {
       if (ex.Number == 2627) // Error number for "Violation of PRIMARY KEY constraint"
       {
           Console.WriteLine("Error: Duplicate primary key"); // Handle the exception
       }
       else
       {
           Console.WriteLine("Error: " + ex.Message); // Handle other SQL exceptions
       }
   }

---

Example of Correct Code

using System;
using System.Data.SqlClient;

public class Program
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=MyDatabase;User Id=myUsername;Password=myPassword;";

        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var command = new SqlCommand("INSERT INTO MyTable (Id, Name) VALUES (2, 'Jane')", connection); // Use a unique primary key
                command.ExecuteNonQuery(); // Insert data
                Console.WriteLine("Data inserted successfully");
            }
        }
        catch (SqlException ex)
        {
            if (ex.Number == 2627) // Error number for "Violation of PRIMARY KEY constraint"
            {
                Console.WriteLine("Error: Duplicate primary key"); // Handle the exception
            }
            else
            {
                Console.WriteLine("Error: " + ex.Message); // Handle other SQL exceptions
            }
        }
    }
}

---