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

Question

I have a user table that he can be either a teacher or headmaster only. 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

Answer 1

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 ;