MySQL check ENUM column in another table throughout constraint

Question

I have two tables:

--
--
--
CREATE TABLE `staff` (
      PRIMARY KEY (`staff_id`),
      `staff_id`    SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      `name`        VARCHAR(50)          NOT NULL,
      `staff_type`  ENUM('expert',        # Perito
                         'injured'        # Danneggiato
                    ) NOT NULL
)ENGINE=InnoDB;

INSERT INTO `x8k1y_staff`
  (`staff_id`, `name`, `staff_type`)
VALUES
  (1, 'Name surname', 'expert'),
  (2, 'John Doe', 'injured');

--
--
--
CREATE TABLE `risk_location` (
    PRIMARY KEY (`risk_loc_id`),
    `risk_loc_id` INT         UNSIGNED NOT NULL AUTO_INCREMENT,
    `injured`     SMALLINT    UNSIGNED NOT NULL,

    CONSTRAINT `fk_injured`
      FOREIGN KEY (`injured`)
      REFERENCES `staff` (`staff_id`)
      ON DELETE CASCADE,
)ENGINE=InnoDB;

But I want to raise an MySQL Error if a user tries to insert a staff_id that doesn't have a value of "injured" in staff_type.

For example:

INSERT INTO risk_location (`injured`)
VALUES (1); # <--- Here I want an error, because the `staff_type` of id 1 is `expert`

INSERT INTO risk_location (`injured`)
VALUES (2); # <--- This is ok

Thank you

Answer 1

You can't do that with a constarint

but you can use a trigger

eventually you need a DELIMITER before and after, depending how you add a trigger

CREATE TABLE `staff` (
      PRIMARY KEY (`staff_id`),
      `staff_id`    SMALLINT    UNSIGNED NOT NULL AUTO_INCREMENT,
      `name`        VARCHAR(50)          NOT NULL,
      `staff_type`  ENUM('expert',        # Perito
                         'injured'        # Danneggiato
                    ) NOT NULL
)ENGINE=InnoDB;

INSERT INTO `staff`
  (`staff_id`, `name`, `staff_type`)
VALUES
  (1, 'Name surname', 'expert'),
  (2, 'John Doe', 'injured');

CREATE TABLE `risk_location` (
    PRIMARY KEY (`risk_loc_id`),
    `risk_loc_id` INT         UNSIGNED NOT NULL AUTO_INCREMENT,
    `injured`     SMALLINT    UNSIGNED NOT NULL,

    CONSTRAINT `fk_injured`
      FOREIGN KEY (`injured`)
      REFERENCES `staff` (`staff_id`)
      ON DELETE CASCADE
)ENGINE=InnoDB;

CREATE TRIGGER before_risk_location_insert
BEFORE INSERT
ON risk_location FOR EACH ROW
BEGIN
    DECLARE _staff_type varchar(10);
    
    SELECT staff_type 
    INTO _staff_type
    FROM staff
    WHERE staff_id = NEW.injured;
    
    IF _staff_type =  'expert' THEN
        signal sqlstate '45000' set message_text = 'user is expert'; 

    END IF; 

END

INSERT INTO risk_location (`injured`)
VALUES (1); # <--- Here I want an error, because the `staff_type` of id 1 is `expert`

user is expert

INSERT INTO risk_location (`injured`)
VALUES (2); # <--- This is ok

db<>fiddle here