问题所在：一个字段外键绑定多张表（comment和source）

一开始我很可笑的以为如果一个字段通过外键绑定多张表后，只有某一张表有值即为满足条件，后来我在写Java类的时候越想越不对，外键的设定好像不是这么玩的，于是的就去实验了一下~

果不其然，这种情况需要满足多张表同时拥有才可以~（哭）

面临的抉择：推到重做，或者另谋出路

我不知道别人是咋样的，反正个人是属于不到万不得已不会重来一遍的那种类型（懒）

自定义设计

首先对于comment和source的外键（后来好像还有一个praise也是）
总之：不该有的外键统统干掉

但是干掉之后怎么保证数据库的完整性呢

我仔细考虑了外键的作用：在插入的时候一定要关联表中存在了外键插入的值才能成功插入

于是我就自然而然地想到了一个思路：模拟外键，但是要去掉所有表都满足这个条件。即标题指出的，一个字段关联多张表。

一个字段关联多张表的设计思路

为了在插入的时候验证数据在某一张表中存在与否，我使用了触发器,关于触发器的概念在此不多赘述（主要是怕说不清，如果不是很了解可以看一下这位博主的文章）

触发器的设计

-- praise       发生在之前，存在，则插入，不存在则抛出异常

DROP TRIGGER IF EXISTS `praise_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `praise_beforeInsert` BEFORE INSERT ON `praise` FOR EACH ROW BEGIN
IF new.type_id=1 THEN 
    IF (SELECT COUNT(*) FROM user_comment WHERE comment_id =new.foreign_id)=1 THEN 
        UPDATE user_comment SET like_num=like_num+1 WHERE comment_id =new.foreign_id;
    ELSE 
        signal SQLSTATE 'HY000' SET message_text = '手动抛出异常' ;
    END IF;
ELSEIF new.type_id=2 THEN
    IF (SELECT COUNT(*) FROM dish WHERE dish_id =new.foreign_id)=1 THEN 
        UPDATE dish SET like_num=like_num+1 WHERE dish_id =new.foreign_id;
    ELSE 
        signal SQLSTATE 'HY000' SET message_text = '手动抛出异常2' ;
    END IF;
END IF;
END
;;
DELIMITER ;
insert into praise(user_id,foreign_id,type_id) value(1,16,2);

-- user_comment         测试id是否存在，若不存在则报错

DROP TRIGGER IF EXISTS `comment_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `comment_beforeInsert` BEFORE INSERT ON `user_comment` FOR EACH ROW BEGIN
IF (SELECT COUNT(*) FROM user_comment WHERE new.parent_comment_id =comment_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '上级评论不存在' ;
ELSE
    IF new.type_id=1 THEN 
        IF (SELECT COUNT(*) FROM `dish_custom` WHERE custom_id =new.foreign_id)=0 THEN 
            signal SQLSTATE 'HY000' SET message_text = '菜品习俗id不存在' ;
        END IF;
    ELSEIF new.type_id=2 THEN
        IF (SELECT COUNT(*) FROM `dish_history` WHERE history_id =new.foreign_id)=0 THEN 
            signal SQLSTATE 'HY000' SET message_text = '菜品历史id不存在' ;
        END IF;
    ELSEIF new.type_id=3 THEN
        IF (SELECT COUNT(*) FROM `dish_literary` WHERE literary_id =new.foreign_id)=0 THEN 
            signal SQLSTATE 'HY000' SET message_text = '菜品典故id不存在' ;
        END IF;
    ELSEIF new.type_id=4 THEN
        IF (SELECT COUNT(*) FROM `dish_recipes` WHERE recipes_id =new.foreign_id)=0 THEN 
            signal SQLSTATE 'HY000' SET message_text = '菜品做法id不存在' ;
        END IF;
    ELSEIF new.type_id=5 THEN
        IF (SELECT COUNT(*) FROM `restaurant` WHERE restaurant_id =new.foreign_id)=0 THEN 
            signal SQLSTATE 'HY000' SET message_text = '菜馆id不存在' ;
        END IF;
    END IF;
END IF;
END
;;
DELIMITER ;


INSERT INTO `testcc`.`user_comment`
            (`comment_id`,
             `user_id`,
             `foreign_id`,
             `parent_comment_id`,
             `time`,
             `content`,
             `like_num`,
             `status`,
             `type_id`)
VALUES (NULL,
        1,
        1,
        1,
        NULL,
        'content',
        0,
        1,
        5);
        
INSERT INTO `testcc`.`user_comment`
            (`comment_id`,
             `user_id`,
             `foreign_id`,
             `parent_comment_id`,
             `time`,
             `content`,
             `like_num`,
             `status`,
             `type_id`)
VALUES (NULL,
        1,
        100,
        1,
        NULL,
        'content',
        0,
        1,
        5);

        
- source 只有在id存在的时候才能插入，否则报错

DROP TRIGGER IF EXISTS `source_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `source_beforeInsert` BEFORE INSERT ON `source` FOR EACH ROW BEGIN
IF new.type_id=1 THEN 
    IF (SELECT COUNT(*) FROM `dish_custom` WHERE custom_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜品习俗id不存在' ;
    END IF;
ELSEIF new.type_id=2 THEN
    IF (SELECT COUNT(*) FROM `dish_history` WHERE history_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜品历史id不存在' ;
    END IF;
ELSEIF new.type_id=3 THEN
    IF (SELECT COUNT(*) FROM `dish_literary` WHERE literary_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜品典故id不存在' ;
    END IF;
ELSEIF new.type_id=4 THEN
    IF (SELECT COUNT(*) FROM `dish_recipes` WHERE recipes_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜品做法id不存在' ;
    END IF;
ELSEIF new.type_id=5 THEN
    IF (SELECT COUNT(*) FROM `restaurant` WHERE restaurant_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜馆id不存在' ;
    END IF;
ELSEIF new.type_id=6 THEN
    IF (SELECT COUNT(*) FROM `user_comment` WHERE comment_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '评论id不存在' ;
    END IF;
ELSEIF new.type_id=7 THEN
    IF (SELECT COUNT(*) FROM `game_step` WHERE id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '游戏步骤id不存在' ;
    END IF;
ELSEIF new.type_id=8 THEN
    IF (SELECT COUNT(*) FROM `commodity` WHERE commodity_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '商品id不存在' ;
    END IF;
ELSEIF new.type_id=0 THEN
    IF (SELECT COUNT(*) FROM `dish` WHERE dish_id =new.foreign_id)=0 THEN 
        signal SQLSTATE 'HY000' SET message_text = '菜品id不存在' ;
    END IF;
END IF;
END
;;
DELIMITER ;


INSERT INTO `testcc`.`source`
            (`source_id`,
             `foreign_id`,
             `url`,
             `source_preview_url`,
             `type_id`)
VALUES (NULL,
        1,
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        '0');

INSERT INTO `testcc`.`source`
            (`source_id`,
             `foreign_id`,
             `url`,
             `source_preview_url`,
             `type_id`)
VALUES (NULL,
        100,
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        '0');