Click here to Skip to main content
15,891,805 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
See more:
Hi, when i try to insert information into the table it gives me this error : The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Confectionery_ConfectioneryCategory". The conflict occurred in database "Workshop", table "dbo.ConfectioneryCategory", column 'ID'.

Can you help me to solve it ?

SQL
CREATE DATABASE Workshop;
USE Workshop;

CREATE TABLE Confectionery (
ID INT IDENTITY NOT NULL PRIMARY KEY,
NameProduct NVARCHAR(50) NOT NULL,
Category INT NOT NULL,
Quantity SMALLINT NOT NULL,
Price DECIMAL(20)
);

CREATE TABLE ConfectioneryCategory (
ID INT IDENTITY NOT NULL PRIMARY KEY,
CategoryName NVARCHAR(50) NOT NULL,
);

CREATE TABLE Sales (
SaleID INT IDENTITY NOT NULL,
Quantity INT NOT NULL,
DateOfSale DATE,
IDPastryChef INT NOT NULL,
IDProduct INT NOT NULL,
PRIMARY KEY(SaleID, IDProduct )
);

CREATE TABLE PastryChef (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
ManifacturedProduct NVARCHAR(50)
);

ALTER TABLE Sales
ADD CONSTRAINT FK_Confectionery_Sales FOREIGN KEY (IDProduct)
REFERENCES Confectionery (ID)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Confectionery
ADD CONSTRAINT FK_Confectionery_ConfectioneryCategory FOREIGN KEY (Category)
REFERENCES ConfectioneryCategory (ID)
ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Sales
ADD CONSTRAINT FK_Sales_PastryChef FOREIGN KEY (IDPastryChef)
REFERENCES PastryChef (ID)
ON DELETE CASCADE
ON UPDATE CASCADE
;

INSERT INTO Confectionery(NameProduct,Category,Quantity,Price )
VALUES('Donuts', 1, 50,'0.99');


What I have tried:

I don't know if my mistake is when i try to create connections or when i insert into the table
Posted
Updated 17-Nov-21 2:46am

You're trying to insert a Confectionery row with category 1. But there is no category with ID 1 in your <code.confectionerycategory< code=""> table.
 
Share this answer
 
To add to what Richard has said, a foreign key is a way to tell SQL that two tables are related: a value in a Confectionary table row is related to a single value in the Category table, in much the same way as an Invoice Line describing a sold product is related by a Foreign key to the Invoices table by the Invoice Number: an invoice will describe several sold items, but there is only ever one Invoice related to that entire group of Invoice Lines.

A foreign key can only be set to a value that exists in the other table, or your data become inconsistent: you have to create the Invoice before you add lines to it!

Similarly, your Category needs to be setup before you start adding items to the Confectionary table that refer to them.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900