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
– George Joseph, Commented Nov 20, 2018 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
– Henrov, Commented Nov 20, 2018 at 16:02

JohnHC · Accepted Answer · 2018-11-20 16:01:36Z

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, 2018 at 16:01

answered Nov 20, 2018 at 15:48

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dane Cameron Over a year ago

Ahh yes, my question has been updated. I am receiving this error when trying to do so.

Dane Cameron Over a year ago

Mmm okay, I added my FK_Pizza to the question.

Henrov · Accepted Answer · 2018-11-20 16:08:42Z

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, 2018 at 16:08

Henrov

1,6801 gold badge30 silver badges62 bronze badges

1 Comment

Dane Cameron Over a year ago

I had to do the same thing for my FK_Toppings but yes that worked. Thank you for the help :)

NicVerAZ · Accepted Answer · 2018-11-20 16:14:21Z

0

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?

answered Nov 20, 2018 at 16:14

NicVerAZ

4072 gold badges4 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL Server - Inserting Multiple Values Into Column

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related