Simple Stored Procedure in Sql Server: Inserting into Bridge Table
I have an issue with the below stored procedure. It runs fine as long as I don't uncomment the last insert INSERT. If I were to uncomment the last INSERT, I get the following error:
Msg 547, Level 16, State 0, Procedure InsertRecipeWithTags, Line 42 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Tag_TagRecipe". The conflict occurred in database "RecipeBox", table "RecipeDetails.Tag", column 'tagID'. The statement has been terminated.
For clarification, I have three tables:
recipe(id, title, introduction, directions)
recipeTag(id, recipeID, tagID)
tag(id, name)
So if I try to insert into the recipeTag
table I get the above error. Please advise.
Thanks.
CREATE PROCEDURE [RecipeDetails].[InsertRecipeWithTags]
/*
variables that map to columns
*/
@title varchar(50),
@intro varchar(255),
@directions varchar(2200),
@ingredients varchar(2200),
@difficulty varchar(6), /*need check constraint setting difficulty to "beginner" "medium" or "expert"*/
@prepTimeHour tinyint,
@prepTimeMinute tinyint,
@inactiveTimeHour tinyint,
@inactiveTimeMinute tinyint,
@servings tinyint,
@photo varbinary(MAX),
@tagName varchar(50),
@tagdescription varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RecipeID int, @TagID int
INSERT INTO RecipeDetails.Recipe (title, intro,directions, ingredients, difficulty,
prepTimeHour, prepTimeMinute, inactiveTimeHour, inactiveTimeMinute, servings, photo)
VALUES (@title, @intro,@directions, @ingredients, @difficulty, @prepTimeHour, @prepTimeMinute,
@inactiveTimeHour, @inactiveTimeMinute, @servings, @photo)
SELECT @RecipeID=SCOPE_IDENTITY()
SELECT * FROM RecipeDetails.Recipe WHERE recipeID = @RecipeID;
INSERT INTO RecipeDetails.Tag (name, description)
VALUES (@tagName, @tagdescription)
SELECT @TagID=SCOPE_IDENTITY()
SELECT * FROM RecipeDetails.Tag WHERE tagID = @TagID;
/*INSERT INTO RecipeDetails.TagRecipe (tagID, recipeID)
VALUES (@RecipeID, @TagI开发者_运维技巧D)*/
END
Reverse the order?
INSERT INTO RecipeDetails.TagRecipe (tagID, recipeID)
VALUES (@TagID, @RecipeID)
You had then the wrong way around
精彩评论