Violation of Unique Constraint

Question

I am trying to execute a stored procedure that inserts a new customer into the customers table and subsequently creates a new association with an existing customer in the associations table.

This must be done as one procedure.

I am getting a violation of the unique constraint on the Associations table which is across Customer1Id, Customer2Id and AssociationType (Which is also an id that refers to a reference table of types). Two customers can be associated with each other many times but not via the same association type. Here is the table to demonstrate this:

Associations Table

The stored procedure is as follows:


CREATE PROCEDURE usp_CreateNewCustomer_Association
    @CustomerType INT,
    @FirstName VARCHAR(30),
    @LastName VARCHAR(30),
    @CompanyName VARCHAR(40) = NULL, 
    @AddressLine1 VARCHAR(30),
    @AddressLine2 VARCHAR(20) = NULL,
    @City VARCHAR(20),
    @Country VARCHAR(30),
    @DOB DATE = NULL,
    @Customer2Id INT,
    @AssociationType INT
AS
BEGIN
    INSERT INTO Customers
(CustomerType, FirstName, LastName, CompanyName, AddressLine1, AddressLine2, City, Country, DOB)
    VALUES (@CustomerType, @FirstName, @LastName, @CompanyName, @AddressLine1, @AddressLine2, @City, @Country, @DOB)

    UPDATE Associations
    SET Customer1Id = @@IDENTITY,
        Customer2Id = @Customer2Id,
        AssociationType = @AssociationType
END

And the execution query is (with comments):

EXEC usp_CreateNewCustomer_Association
         @CustomerType = 1,         -- Personal Customer code = '1'
         @FirstName = 'Henry',
         @LastName = 'Godfrey',
         @AddressLine1 = 'Tripton Heights',
         @AddressLine2 = 'Broadspoke',
         @City = 'Sydney',
         @Country = 'Australia',
         @Customer2Id = 3,          -- There is an existing customer with the ID '3'
         @AssociationType = 43      -- Association type 43 means 'Developer' as in Customer (num) is the developer for customer 3

And I get this error:

Msg 2627, Level 14, State 1, Procedure usp_CreateNewCustomer_Association, Line 22
Violation of UNIQUE KEY constraint 'UC_Associations'. Cannot insert duplicate key in object 'dbo.Associations'. The duplicate key value is (14, 3, 43).

I am not very familiar with working with unique constraints (that's probably obvious), but I am not allowed to drop the unique constraint so if anyone could advise me on how to correct this I would greatly appreciate it.

if an answer solved your question you should mark it as correct. This makes sure that everyone knows that this question is solved. — Daan Seuntjens

Unknown Unknown · Accepted Answer · 2019-12-07T17:40:17

Your UPDATE statement always tries to update ALL columns as you do not have a WHERE condition.

You should either change the UPDATE statement to include a WHERE condition or change this to an INSERT - as I do not fully understand how this should work with only UPDATE statements.

Violation of Unique Constraint

2 Answers