Violation of PRIMARY KEY constraint

 This is SQL error 2627 - violation of Primary Key constraint.

A "Violation of PRIMARY KEY constraint" error occurs when you try to insert a duplicate value into a column that has been defined as a primary key. Primary keys must contain unique values for each row in a table. This error can also occur when trying to update a primary key column to a value that already exists in another row.

Steps to Resolve the Error:

• Identify the Duplicate Data: Find out which value is causing the violation. You can use a query to check for duplicates in the column.

SELECT PrimaryKeyColumn, COUNT(*)
FROM YourTable
GROUP BY PrimaryKeyColumn
HAVING COUNT(*) > 1;

• Check for Existing Data Before Insert: Before inserting a new row, check if the primary key value already exists in the table.

SELECT *
FROM YourTable
WHERE PrimaryKeyColumn = 'ValueYouWantToInsert';

• Correct the Data: Ensure that the data you are trying to insert or update is unique in the primary key column.
• Modify the Insert/Update Logic: Depending on your use case, you may need to modify your logic to handle duplicates appropriately. Here are a few options:

Skip Insertion if Duplicate: Insert only if the primary key value does not already exist.

IF NOT EXISTS (SELECT 1 FROM YourTable WHERE PrimaryKeyColumn = 'ValueYouWantToInsert')
BEGIN
    INSERT INTO YourTable (PrimaryKeyColumn, OtherColumns)
    VALUES ('ValueYouWantToInsert', OtherValues);
END;

Update Existing Row if Duplicate: Update the row if the primary key value already exists.

IF EXISTS (SELECT 1 FROM YourTable WHERE PrimaryKeyColumn = 'ValueYouWantToInsert')
BEGIN
    UPDATE YourTable
    SET OtherColumn = 'NewValue'
    WHERE PrimaryKeyColumn = 'ValueYouWantToInsert';
END
ELSE
BEGIN
    INSERT INTO YourTable (PrimaryKeyColumn, OtherColumns)
    VALUES ('ValueYouWantToInsert', OtherValues);
END;

• Review Application Logic: Ensure that your application logic correctly handles primary key values, avoiding attempts to insert duplicates.

Example:

Let's say you have a table named Employees with EmployeeID as the primary key, and you are trying to insert a new employee:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Position NVARCHAR(50)
);

-- Attempting to insert a new employee
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position)
VALUES (1, 'John', 'Doe', 'Developer');

-- Attempting to insert another employee with the same EmployeeID
INSERT INTO Employees (EmployeeID, FirstName, LastName, Position)
VALUES (1, 'Jane', 'Smith', 'Manager');

The second insert will fail with a "Violation of PRIMARY KEY constraint" error because EmployeeID 1 already exists.

Handling the Error:

Skip Insertion if Duplicate:

IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = 1)
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Position)
    VALUES (1, 'Jane', 'Smith', 'Manager');
END;

Update Existing Row if Duplicate:

IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = 1)
BEGIN
    UPDATE Employees
    SET FirstName = 'Jane', LastName = 'Smith', Position = 'Manager'
    WHERE EmployeeID = 1;
END
ELSE
BEGIN
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Position)
    VALUES (1, 'Jane', 'Smith', 'Manager');
END;

Conclusion

By following these steps, you can effectively handle "Violation of PRIMARY KEY constraint" errors, ensuring the integrity and uniqueness of your primary key data in SQL Server.

The DELETE statement conflicted with the REFERENCE constraint

 The error message "The DELETE statement conflicted with the REFERENCE constraint" indicates that you are attempting to delete a row from a table, but there are one or more rows in a related table that reference this row through a foreign key constraint. SQL Server is preventing the delete to maintain referential integrity.

Steps to Resolve the Error:

• Identify the Foreign Key Constraint: You need to find out which table and foreign key constraint are causing the conflict.

SELECT
    fk.name AS ForeignKey,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM
    sys.foreign_keys AS fk
INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.tables AS tr ON fk.referenced_object_id = tr.object_id
INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns AS cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN sys.columns AS cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE
    tp.name = 'YourTableName';

• Check the Data in the Referencing Table: Identify the rows in the referencing table that are causing the conflict.

SELECT *
FROM ReferencingTable
WHERE ForeignKeyColumn = 'ValueOfKeyToBeDeleted';

• Decide on an Action: Based on your requirements, you can take one of the following actions:

1. Cascade Delete: Automatically delete the related rows in the referencing table.
2. Manual Delete: Manually delete the related rows in the referencing table.
3. Nullify the Foreign Key: Set the foreign key columns to NULL in the referencing table.
4. Prevent Deletion: Do not allow the delete if related rows exist.

Example Solutions:

1. Cascade Delete:

Modify the foreign key constraint to include ON DELETE CASCADE. This will automatically delete the related rows in the referencing table when the parent row is deleted.

ALTER TABLE ReferencingTable
DROP CONSTRAINT FK_ConstraintName;

ALTER TABLE ReferencingTable
ADD CONSTRAINT FK_ConstraintName
FOREIGN KEY (ForeignKeyColumn)
REFERENCES ParentTable(PrimaryKeyColumn)
ON DELETE CASCADE;

2. Manual Delete:

Delete the related rows from the referencing table before deleting the row from the parent table.

BEGIN TRANSACTION;

DELETE FROM ReferencingTable
WHERE ForeignKeyColumn = 'ValueOfKeyToBeDeleted';

DELETE FROM ParentTable
WHERE PrimaryKeyColumn = 'ValueOfKeyToBeDeleted';

COMMIT TRANSACTION;

3. Nullify the Foreign Key:

Set the foreign key columns to NULL in the referencing table before deleting the row from the parent table.

BEGIN TRANSACTION;

UPDATE ReferencingTable
SET ForeignKeyColumn = NULL
WHERE ForeignKeyColumn = 'ValueOfKeyToBeDeleted';

DELETE FROM ParentTable
WHERE PrimaryKeyColumn = 'ValueOfKeyToBeDeleted';

COMMIT TRANSACTION;

4. Prevent Deletion:

If it is important to maintain the integrity of the data and prevent the deletion of rows with related data, you can use a check to prevent the deletion.

IF EXISTS (SELECT 1 FROM ReferencingTable WHERE ForeignKeyColumn = 'ValueOfKeyToBeDeleted')
BEGIN
    RAISERROR ('Cannot delete the row because it is referenced in another table.', 16, 1);
END
ELSE
BEGIN
    DELETE FROM ParentTable
    WHERE PrimaryKeyColumn = 'ValueOfKeyToBeDeleted';
END;

Conclusion:

Choose the appropriate solution based on your application's requirements and data integrity needs. Make sure to thoroughly test the chosen approach in a development environment before applying it to your production database.