SQL Server - Inserting Multiple Values Into Column

Question

I'm trying to update my pizza so it will allow more than one topping. I am receiving the error Cannot insert duplicate key row in object 'dbo.PizzaToppings' with unique index 'FK_Pizza'. The duplicate key value is (3). when trying to add more than one toppingID to the column. How can I add multiple ToppingID's?

INSERT INTO dbo.PizzaToppings
(PizzaToppingsID, PizzaID, ToppingsID)
VALUES 
   (1, 1, 1),
   (2, 2, 2),
   (3, 3, 3),
   (4, 4, 7),
   (5, 5, 6),
   (6, 6, 4),
   (7, 3, 7);

The FK_Pizza that was created

CREATE UNIQUE NONCLUSTERED INDEX
FK_Pizza
ON PizzaToppings
(
PizzaID ASC
);

Do you mean to say you are interested in storing the pizzatoppings as a comma_seperated string in the field ToppingsID?. It would be a bad design to have it like that. Instead store the PizzaToppingsID and ToppingsId is a seperate table — George Joseph, Nov 20 '18 at 15:47
I would like to see that index FK_Pizza. Basically it says u are trying to insert a record that is not distuingishable from other records mentioned in the index FK_Pizza. Can you supply us with a create-statement for that table? — Henrov, Nov 20 '18 at 16:02

JohnHC · Answer 1 · 2018-11-20T16:01:36.077

2

You're trying to store an array into a what looks like a number column.

Usually you would have a row for each individual topping:

Pizzas:
PizzaID     integer
PizzaName   varchar(32)

Toppings:
ToppingID   integer
ToppingName varchar(32)

PizzaTopping:
PizzaID     integer (fk to Pizzas.PizzaID)
ToppingID   integer (fk to Toppings.ToppingID)

PizzaTopping would contain a row for each topping per pizza.

Edit for updated question:

Your error is down to a duplicate key violation. Check the constraints on the table, it looks like there is a constraint that prevents you adding more than one row per pizzaID

edited Nov 20 '18 at 16:01

answered Nov 20 '18 at 15:48

JohnHC

10,935
1
24
40

Ahh yes, my question has been updated. I am receiving this error when trying to do so. – Cameron Cole Nov 20 '18 at 15:55
Mmm okay, I added my FK_Pizza to the question. – Cameron Cole Nov 20 '18 at 16:06

score 1 · Accepted Answer · answered Nov 20 '18 at 16:08

1

Each Pizza has to be Unique..... Try this:

CREATE UNIQUE NONCLUSTERED INDEX
FK_Pizza
ON PizzaToppings
(
PizzaID ASC, ToppingsID ASC
);

Don't forget to drop the constraint first

answered Nov 20 '18 at 16:08

Henrov

1,610
1
24
52

1

I had to do the same thing for my FK_Toppings but yes that worked. Thank you for the help :) – Cameron Cole Nov 20 '18 at 16:13

score 0 · Answer 3 · answered Nov 20 '18 at 16:14

Why is this clustered index named like a foreign key constraint?

CREATE UNIQUE NONCLUSTERED INDEX
FK_Pizza
ON PizzaToppings
(
PizzaID ASC
);

Does it have to be unique? Don't you mean this to just be an FK and not an index?

Shouldn't your unique key be PizzaToppingID?

Are you allowing identity insert? Or shouldn't you let it be an autoincremented ID and just insert PizzaID and ToppingID?

Also is there already a record with PizzaID = 3 already in this table? If you are looking to just maintain or UPSERT, then shouldn't a MERGE be better?

If this is a junction table between Pizza and topping, to show which toppings are allowed on which pizza, shouldn't it have 2 FKs and 1 unique index?

SQL Server - Inserting Multiple Values Into Column

3 Answers3