0

Forgive me as I'm new to databases. I have a recipe form that the user can enter several ingredients and the recipe name into. With this form I have the following tables: Recipe{recipeID, recipeName} RecipeIngredient{recipeID,ingredientID,measurement} Ingredient{ingredientID, ingredientName}

RecipeIngredients acts as an intermediate table for the one to many relationship (one recipe to many ingredients). I assigned recipeID as a foreign key inside that table to the primary key recipeID in the recipe table. I thought a foreign key would update based on the primary key updating, but if I insert into the Recipe table the RecipeIngredients table does nothing.

The recipe and the ingredients are assigned ID's automatically.I'm trying to link the ID of one recipe to the auto assigned ID's of each ingredient. For example if the user enters in 8 ingredients, a unique ID is assigned to each of those ingredients. I need the RecipeIngredients table to duplicate those ID's and associate them with the auto-assigned Recipe ID.

I've tried several solutions for updating a column based on changes in other tables. I've tried modifying this MySQL update statement and also modifying this SQL server but I can't seem to get either of them to work.

Suggestions?

Community
  • 1
  • 1
Lohkii
  • 43
  • 1
  • 2
  • 10

1 Answers1

0

In general, you will need to get the new recipeID from the first insert, then use that when you insert into the ingredients table.

Do you have access to the newly inserted recipeID from the insert into the recipe table? How are you doing this insert?

Here is an example Stored Procedure which goes into the database to do the insert / update and return the id:

CREATE PROCEDURE [dbo].[spAlertCategoryInsUpd]
    @AlertCategoryID int,
    @AlertID int,
    @CategoryID int,
    @ScreenSortOrder int
AS

SET NOCOUNT ON

IF EXISTS(SELECT [AlertCategoryID] FROM [dbo].[AlertCategories] WHERE [AlertCategoryID] = @AlertCategoryID)
BEGIN
    UPDATE [dbo].[AlertCategories] SET
        [AlertID] = @AlertID,
        [CategoryID] = @CategoryID,
        [ScreenSortOrder] = @ScreenSortOrder
    WHERE
        [AlertCategoryID] = @AlertCategoryID
    SELECT  @AlertCategoryID as AlertCategoryID;    
END
ELSE
BEGIN
    INSERT INTO [dbo].[AlertCategories] (
        [AlertID],
        [CategoryID],
        [ScreenSortOrder]
    ) 
    OUTPUT INSERTED.AlertCategoryID AS AlertCategoryID
    VALUES (
        @AlertID,
        @CategoryID,
        @ScreenSortOrder
    )
    ;
END
Decker97
  • 1,643
  • 10
  • 11
  • I'm getting the recipeName from a textbox then I have the table auto assigning ID's when I save the recipe to the database. Using `string recipeQuery = "INSERT INTO Recipe (recipeName) VALUES ( '"; recipeQuery += tbRecipeName.Text + "')"; CreateCommandObject(recipeQuery, ref databaseConnection);` Do I have to do another insert into RecipeIngredients after this ID is assigned or can I do an update and have the ID also get assigned to the RecipeIngredients table? – Lohkii Mar 01 '13 at 21:44
  • I use stored procs for updating and inserting with parameters for each field. The return value from the stored proc gives me the new id. – Decker97 Mar 01 '13 at 21:48
  • Are you referring to a function for updating and inserting with the ID as a return value? – Lohkii Mar 04 '13 at 00:01