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 ?
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