mysql - 如何使用 Innodb 确保 MySQL 中的多个表列是唯一的(或重构我的表)？

Question

我目前有两个单独的表，还需要添加第三个表，用于存储食谱、项目和 custom_meal(要创建的表)。

我希望创建一个膳食计划，用户可以在其中添加食谱、项目或自定义项目作为特定日期的早餐/午餐/晚餐。

我对下面的新表格有一个想法，尽管沿着这条路走下去，我无法确保meal_planner.id只能是食谱、项目或自定义。
通过编写良好的查询，我意识到这永远不应该成为一个问题，尽管作为一个仍然需要大量了解数据库的人，我更喜欢一个解决方案，确保数据永远不会在不应该输入的时候输入。

我已经使用的表中当前感兴趣的列是:

recipe.id  
item.id
user.id

我目前对“膳食计划”表的想法是:

表:meal_planner

id - primary key
user_id - foreign key user.id
mdate - the day which the meal is planned for
mtime - whether the meal is breakfast, lunch or dinner.
mcomment (can be null)

表:meal_is_recipe

meal_id - foreign key meal_planner.id
recipe_id - foreign key recipe.id

表:meal_is_custom

meal_id - foreign key meal_planner.id
custom_id - foreign key custom_meal.id

表:meal_is_item

meal_id - foreign key meal_planner.id
item_id - foreign key item.id

表:custom_meal

id - primary key
user_id - foreign key user.id
name - varchar to hold the name of the meal

这应该允许我使用联接来获取显示所需的所有数据，尽管如上所述，它让我感到烦恼，因为它们没有限制阻止在食谱、项目和/或自定义中使用meal.id。

Answer 1

好吧，自从发布这篇文章以来，我发现了“触发器”。

触发器类似于存储过程，尽管在触发器情况下，它们会在满足设置条件时自动触发，例如在完成表上的插入、更新或删除之前。

在我的例子中，我创建了六个触发器，每个 meal_is_x 表有两个触发器，用于检查 meal_id 是否已存在于其他表之一中，如果存在，则返回错误而不是插入。

我没有通过使用示例的 phpmyadmin 查询获得任何运气，因此我通过选择数据库，单击“触发器”选项卡，单击“添加新触发器”，然后输入以下内容在 phpmyadmin 中创建了触发器(针对每个不同的表稍作修改)以及是否用于插入或更新)。

触发器名称:meal_is_recipe_BeforeInsert

表:meal_is_recipe

时间:之前

事件:INSERT

定义:

BEGIN
    DECLARE custom_match INT;
    DECLARE item_match INT;
    SELECT COUNT(1) INTO custom_match FROM meal_is_custom
    WHERE meal_id = NEW.meal_id;
    IF custom_match > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'A Custom record with that meal id already exists';
    ELSE
        SELECT COUNT(1) INTO item_match FROM meal_is_item
        WHERE meal_id = NEW.meal_id;
        IF item_match > 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'An Item record with that meal id already exists';
        END IF;
    END IF;
END

定义者:如果可以运行触发器，我将其留空，作为进行查询的用户。

我相信我也应该能够通过 MySQL 查询得到相同的结果，但正如我提到的，这在 phpmyadmin 中并不能立即对我起作用，而不是进一步研究它，触发器选项卡足够简单:

DELIMITER //
CREATE TRIGGER meal_is_recipe_BeforeInsert BEFORE INSERT ON meal_is_recipe
BEGIN
    DECLARE custom_match INT;
    DECLARE item_match INT;
    SELECT COUNT(1) INTO custom_match FROM meal_is_custom
    WHERE meal_id = NEW.meal_id;
    IF custom_match > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'A Custom record with that meal id already exists';
    ELSE
        SELECT COUNT(1) INTO item_match FROM meal_is_item
        WHERE meal_id = NEW.meal_id;
        IF item_match > 0 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'An Item record with that meal id already exists';
        END IF;
    END IF;
END//
DELIMITER ;

正如我在问题中提到的，我在数据库方面的知识还有很长的路要走，所以虽然我已经用最初的问题模式对此进行了测试并且它有效，但我不能保证这是实现结果的最有效方法。