CODEWITHSUNDEEP
sql-server
Damian G.
Damian G.

Reputation: 27

SQL Server - How to add a constraint when a Condition is met

What I am trying to do is make it so a Customer Order cannot have the same Product specified multiple times in a Order. I think I need to check if the CustomerOderId is the same and if it is checked if they have more than one of the same ProductId but I am not sure how to go about it. I have the following tables in my DB

**OrderProduct**
(Id) int
(CustomerOrderId) int
(ProductId) int
(Quantity) int
(ProdcutRating) int

**CustomerOrder**
(Id) int
(CustomerId) int
(AddedDate) datetime

**Product**
(Id) int
(Name) nvarchar(75)
(Price) int
(AddedDate) datetime

I have tried the following but I think it would one; make it so no other customer could ever order the same product and two; would have to delete all current duplicates

ALTER TABLE OrderProduct ADD  CONSTRAINT [IX_UniqueProductId] UNIQUE 
NONCLUSTERED (ProductId ASC)

Anyone?

Upvotes: 0

Views: 139

Answers (2)

Takukan
Takukan

Reputation: 11

If I understood you, you want only one product for each customerOrder in your OrderProduct. To achieve this you should use (productId, CustomerOrderId) as primary key of the OrderProduct table so there is a one to one relationship.

ALTER TABLE OrderProduct ADD  CONSTRAINT [IX_UniqueProductId] UNIQUE 
NONCLUSTERED (ProductId ASC, CustomerOrderID ASC)

Upvotes: 0

jradich1234
jradich1234

Reputation: 1425

If I'm understanding your question correctly (and your data setup), all you would need to do is add the customerOrderId column to your unique index. This would restrict you to having a product only once per customer order.

ALTER TABLE OrderProduct ADD  CONSTRAINT [IX_UniqueProductId] UNIQUE 
NONCLUSTERED (ProductId ASC, CustomerOrderID ASC)

This assumes that every time a customer orders an new Customer Order ID is generated and that the same customer could order the same product on subsequent orders.

Upvotes: 2

Related Questions