👀 Views: 88 💬 Answers: 1 📅 Created: 2025-06-05

sql-server unique-constraint composite-key sql

I'm collaborating on a project where I'm relatively new to this, so bear with me... I'm working with a question with inserting records into a table that has a composite unique constraint in SQL Server 2019. The table schema is as follows: ```sql CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, ProductID INT, OrderDate DATETIME, UNIQUE (CustomerID, ProductID) ); ``` I'm trying to insert new records while ensuring that each CustomerID/ProductID combination is unique. However, I'm working with the following behavior when attempting to insert a duplicate combination: ``` Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'UQ__Orders__CustomerID__ProductID'. want to insert duplicate key in object 'dbo.Orders'. ``` To handle potential duplicates, I implemented a check before the insert: ```sql IF NOT EXISTS ( SELECT 1 FROM Orders WHERE CustomerID = @CustomerID AND ProductID = @ProductID ) BEGIN INSERT INTO Orders (OrderID, CustomerID, ProductID, OrderDate) VALUES (@OrderID, @CustomerID, @ProductID, @OrderDate); END ``` However, despite this check, I'm still working with this behavior. I've tried using a transaction with the above check, but it seems like if two transactions are happening simultaneously with the same CustomerID/ProductID, they can both pass the check before either transaction inserts the record. Is there a better way to ensure that my inserts handle this unique constraint more safely? Or is there a way to catch the behavior and handle it gracefully? I’m using Entity Framework Core 5.0 for data access, and I’m open to using stored procedures if that would help. Any suggestions would be greatly appreciated! Has anyone dealt with something similar?