douzhenggui8171 2017-03-06 09:06
浏览 18
已采纳

映射ER图的专业化,插入子表时重复

I have a user table that he can be either a teacher or headmaster onlyMy ER diagram. I think that disjoint specialization it is the appropriate for it in ER. I'm using MySQL dbms and after I created the tables I created a user in User table(for example UserId=31). But the problem is when I'm inserting a new record to teacher table with a UserId =31 and inserting the same UserId in headmaster table it doesn't give any error. I think this is considered a violation for disjoint property.

I have a user table with this attributes

User(UserId, Username, Password, gender, name, address, education, university, mobile).

and teacher table that it comes after mapping like this

teacher(UserId,courses).

and headmaster table. headmaster(UserId,Years_Of_Service).

When I had created teacher and headmaster tables I created them without a primary key, then I added an index for UserId attribute in each table and make it reference to UserId in User table.

My Question: How I can implement the disjoint for my tables using MySQL?

Thanks in advance

  • 写回答

1条回答 默认 最新

  • douhe6255 2017-03-06 21:55
    关注

    One way of doing it is to add type indicators to each of your tables, as well as FK constraints that include the type indicators.

    I'm just going to show the supertype as well as one subtype table.

    CREATE TABLE `User` (
      `UserID` bigint(21) NOT NULL,
      `UserType` enum('H', 'T') NOT NULL,
      ...
      PRIMARY KEY (`UserID`),
      UNIQUE KEY (`UserID`,`UserType`)
    ) ENGINE=InnoDB;
    
    CREATE TABLE `Teacher` (
      `UserID` bigint(21) NOT NULL,
      `UserType` enum('H', 'T') NOT NULL,
      ...
      PRIMARY KEY (`UserID`),
      FOREIGN KEY (`UserID`, `UserType`) REFERENCES `User` (`UserID`, `UserType`) ON UPDATE CASCADE
    ) ENGINE=InnoDB;
    

    Note that the UserType column must be defined the same way in supertype and subtype tables in order to create an FK constraint. I added a unique key to the User table to support a composite FK constraint in the subtypes. To restrict the type indicator in each subtype table, we then create triggers:

    DELIMITER ;;
    
    CREATE TRIGGER check_teacher_insert BEFORE INSERT ON Teacher
        FOR EACH ROW
        BEGIN
            IF new.UserType != 'T' THEN
                SIGNAL SQLSTATE '45000'   
                SET MESSAGE_TEXT = 'Invalid UserType in Teacher';
            END IF;
        END;
    ;;
    
    CREATE TRIGGER check_teacher_update BEFORE UPDATE ON Teacher
        FOR EACH ROW
        BEGIN
            IF new.UserType != 'T' THEN
                SIGNAL SQLSTATE '45000'   
                SET MESSAGE_TEXT = 'Invalid UserType in Teacher';
            END IF;
        END;
    ;;
    
    DELIMITER ;
    

    An alternative approach is to leave out the type indicators, and just use triggers:

    DELIMITER ;;
    
    CREATE TRIGGER check_teacher_insert BEFORE INSERT ON Teacher
        FOR EACH ROW
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM Headmaster
                WHERE UserID = new.UserID
            ) THEN
                SIGNAL SQLSTATE '45000'   
                SET MESSAGE_TEXT = 'Invalid UserID in Teacher - already exists in HeadMaster';
            END IF;
        END;
    ;;
    
    CREATE TRIGGER check_teacher_update BEFORE UPDATE ON Teacher
        FOR EACH ROW
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM Headmaster
                WHERE UserID = new.UserID
            ) THEN
                SIGNAL SQLSTATE '45000'   
                SET MESSAGE_TEXT = 'Invalid UserID in Teacher - already exists in HeadMaster';
            END IF;
        END;
    ;;
    
    DELIMITER ;
    

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 MATLAB代码补全插值
  • ¥15 Typegoose 中如何使用 arrayFilters 筛选并更新深度嵌套的子文档数组信息
  • ¥15 前后端分离的学习疑问?
  • ¥15 stata实证代码答疑
  • ¥50 husky+jaco2实现在gazebo与rviz中联合仿真
  • ¥15 dpabi预处理报错:Error using y_ExtractROISignal (line 251)
  • ¥15 在虚拟机中配置flume,无法将slave1节点的文件采集到master节点中
  • ¥15 husky+kinova jaco2 仿真
  • ¥15 zigbee终端设备入网失败
  • ¥15 金融监管系统怎么对7+4机构进行监管的
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部