存储过程异常：配料被错误关联至多个菜单项问题求助

场景与问题现象 #

我创建了两个存储过程InsertNewMenuItem和InsertIngredient：

• InsertNewMenuItem负责检查MenuTitle、MenuGroupText是否存在，不存在则自动创建，然后插入新的菜单项
• InsertIngredient用于给指定菜单项添加配料

执行InsertIngredient指定给MenuItemID=2的菜品加配料后，查询结果显示配料被关联到了所有菜单项，但实际只应该关联到目标菜品。

表结构 #

CREATE TABLE [dbo].[Menu](
[ID] INT PRIMARY KEY identity(1,1),
[MenuTitle] [nvarchar](50) NULL,
[MenuDescriptionText] [nvarchar](50) NULL)

CREATE TABLE [dbo].[MenuGroups](
[ID] INT PRIMARY KEY identity(1,1),
[MenuID] INT NOT NULL,
[MenuGroupText] [nvarchar](50) NOT NULL,
[MenuGroupDescriptionText] [nvarchar](50) NULL, 
FOREIGN KEY (MenuID) REFERENCES Menu(ID))

CREATE TABLE [dbo].[MenuItems](
[ID] INT PRIMARY KEY identity(1,1),
[MenuGroupsID] INT NOT NULL,
[MenuItemTitle] [nvarchar](50) NOT NULL,
[MenuItemDescriptionText] [nvarchar](50) NULL,
FOREIGN KEY (MenuGroupsID) REFERENCES MenuGroups(ID))

CREATE TABLE [dbo].[Ingredients](
[ID] INT PRIMARY KEY identity(1,1),
[IngredientTitleText] [NVARCHAR](50) NOT NULL,
[IngredientDescriptionText] [NVARCHAR](200) NULL
)

CREATE TABLE [dbo].[IngredientsInItems](
[ID] INT PRIMARY KEY identity(1,1),
[IngredientsID] INT NOT NULL,
[MenuItemID] INT NOT NULL, 
[DescriptionText] [NVARCHAR](200), 
FOREIGN KEY (IngredientsID) REFERENCES Ingredients(ID),
FOREIGN KEY (MenuItemID) REFERENCES MenuItems(ID))

存储过程与执行语句 #

1. InsertNewMenuItem 存储过程 #

CREATE PROCEDURE InsertNewMenuItem
    @MenuTitle NVARCHAR(50),
    @MenuGroupText NVARCHAR(50),
    @MenuItemTitle NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MenuID INT;

    -- 检查Menu是否存在，不存在则创建
    SELECT @MenuID = ID FROM dbo.Menu WHERE MenuTitle = @MenuTitle;
    IF @MenuID IS NULL
    BEGIN
        INSERT INTO dbo.Menu (MenuTitle, MenuDescriptionText)
        VALUES (@MenuTitle, NULL);
        SELECT @MenuID = SCOPE_IDENTITY();  -- 获取新生成的MenuID
    END

    DECLARE @MenuGroupID INT;

    -- 检查MenuGroup是否存在，不存在则创建
    SELECT @MenuGroupID = ID FROM dbo.MenuGroups WHERE MenuID = @MenuID AND MenuGroupText = @MenuGroupText;
    IF @MenuGroupID IS NULL
    BEGIN
        INSERT INTO dbo.MenuGroups (MenuID, MenuGroupText, MenuGroupDescriptionText)
        VALUES (@MenuID, @MenuGroupText, NULL);
        SELECT @MenuGroupID = SCOPE_IDENTITY();  -- 获取新生成的MenuGroupID
    END

    -- 插入菜单项
    INSERT INTO dbo.MenuItems (MenuGroupsID, MenuItemTitle, MenuItemDescriptionText)
    VALUES (@MenuGroupID, @MenuItemTitle, NULL);
END

执行语句：

EXEC InsertNewMenuItem 'Main Menu', 'Tacos', 'Basic Taco';
EXEC InsertNewMenuItem 'Main Menu', 'Tacos', 'Chicken Cheese Taco';

2. InsertIngredient 存储过程 #

CREATE PROCEDURE InsertIngredient
    @IngredientTitleText NVARCHAR(50),
    @MenuItemID INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IngredientID INT;

    -- 检查配料是否存在，不存在则创建
    SELECT @IngredientID = ID FROM dbo.Ingredients WHERE IngredientTitleText = @IngredientTitleText;
    IF @IngredientID IS NULL
    BEGIN
        INSERT INTO dbo.Ingredients (IngredientTitleText)
        VALUES (@IngredientTitleText);
        SELECT @IngredientID = SCOPE_IDENTITY();  -- 获取新生成的IngredientID
    END

    -- 关联配料到菜单项
    INSERT INTO dbo.IngredientsInItems (IngredientsID, MenuItemID)
    VALUES (@IngredientID, @MenuItemID);
END 

执行语句：

DECLARE @MenuItemID INT = 2; 
EXEC InsertIngredient 'Chicken', @MenuItemID;

问题原因 #

你使用的查询语句select * from Menu, MenuGroups, MenuItems是隐式交叉连接，没有指定表之间的关联条件，会生成所有表的笛卡尔积。当IngredientsInItems中有一条关联MenuItemID=2的记录时，这条记录会和Menu、MenuGroups、MenuItems的所有记录进行匹配，导致看起来配料被加到了所有菜单项，但实际上IngredientsInItems中只有一条正确的关联记录。

解决方案 #

1. 修正查询语句（核心解决） #

使用显式连接指定表之间的外键关联条件，这样就能准确显示每个菜单项对应的配料：

SELECT 
    m.MenuTitle, m.MenuDescriptionText,
    mg.MenuGroupText, mg.MenuGroupDescriptionText,
    mi.MenuItemTitle, mi.MenuItemDescriptionText,
    i.IngredientTitleText, i.IngredientDescriptionText
FROM dbo.Menu m
JOIN dbo.MenuGroups mg ON m.ID = mg.MenuID
JOIN dbo.MenuItems mi ON mg.ID = mi.MenuGroupsID
LEFT JOIN dbo.IngredientsInItems iii ON mi.ID = iii.MenuItemID
LEFT JOIN dbo.Ingredients i ON iii.IngredientsID = i.ID;

• 使用LEFT JOIN可以显示所有菜单项，包括没有配料的；如果只需要查看有配料的菜单项，换成INNER JOIN即可。

2. 优化存储过程，自动返回新菜单项ID #

修改InsertNewMenuItem，添加输出参数返回新创建的MenuItemID，避免手动指定ID出错：

CREATE PROCEDURE InsertNewMenuItem
    @MenuTitle NVARCHAR(50),
    @MenuGroupText NVARCHAR(50),
    @MenuItemTitle NVARCHAR(50),
    @NewMenuItemID INT OUTPUT  -- 新增输出参数
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MenuID INT;
    SELECT @MenuID = ID FROM dbo.Menu WHERE MenuTitle = @MenuTitle;
    IF @MenuID IS NULL
    BEGIN
        INSERT INTO dbo.Menu (MenuTitle, MenuDescriptionText)
        VALUES (@MenuTitle, NULL);
        SELECT @MenuID = SCOPE_IDENTITY();
    END

    DECLARE @MenuGroupID INT;
    SELECT @MenuGroupID = ID FROM dbo.MenuGroups WHERE MenuID = @MenuID AND MenuGroupText = @MenuGroupText;
    IF @MenuGroupID IS NULL
    BEGIN
        INSERT INTO dbo.MenuGroups (MenuID, MenuGroupText, MenuGroupDescriptionText)
        VALUES (@MenuID, @MenuGroupText, NULL);
        SELECT @MenuGroupID = SCOPE_IDENTITY();
    END

    INSERT INTO dbo.MenuItems (MenuGroupsID, MenuItemTitle, MenuItemDescriptionText)
    VALUES (@MenuGroupID, @MenuItemTitle, NULL);
    SELECT @NewMenuItemID = SCOPE_IDENTITY();  -- 返回新菜单项ID
END

调用示例：

DECLARE @NewItemID INT;
EXEC InsertNewMenuItem 'Main Menu', 'Tacos', 'Chicken Cheese Taco', @NewItemID OUTPUT;
EXEC InsertIngredient 'Chicken', @NewItemID;

3. 添加唯一性约束，防止重复数据 #

给相关表添加唯一约束，避免重复创建相同的菜单、菜组、菜单项或配料：

-- 菜单名称唯一
ALTER TABLE dbo.Menu ADD CONSTRAINT UQ_Menu_MenuTitle UNIQUE (MenuTitle);
-- 同一菜单下的菜组名称唯一
ALTER TABLE dbo.MenuGroups ADD CONSTRAINT UQ_MenuGroups_MenuID_GroupText UNIQUE (MenuID, MenuGroupText);
-- 同一菜组下的菜单项名称唯一
ALTER TABLE dbo.MenuItems ADD CONSTRAINT UQ_MenuItems_GroupID_ItemTitle UNIQUE (MenuGroupsID, MenuItemTitle);
-- 配料名称唯一
ALTER TABLE dbo.Ingredients ADD CONSTRAINT UQ_Ingredients_Title UNIQUE (IngredientTitleText);

内容的提问来源于stack exchange，提问作者Kitty Witty