MYSQL Check value of an attribute w/ another table attribute

November 22, 2021

I want to assure at inserting a manager that department manager start date [DEPARTMENT.mgr_start_date] is coming after his birthdate [EMPLOYEE.bdate],
how can I do that?

(
    ssn INT(16) unsigned NOT NULL,
    fname VARCHAR(16),
    lname VARCHAR(16),
    bdate DATE,
    address VARCHAR(32),
    gender enum('m','f'),
    salary decimal(16,2),
    Dno VARCHAR(8),
    PRIMARY KEY (ssn)
);

CREATE TABLE IF NOT EXISTS DEPARTMENT
(
    mgr_ssn INT(16) unsigned,
    Dname VARCHAR(32),
    mgr_start_date DATE,
    Dnumber VARCHAR(8),
    PRIMARY KEY (Dnumber),
    FOREIGN KEY (mgr_ssn) REFERENCES EMPLOYEE(ssn)
);```

>Solution :

You would have to do this with a trigger.

CHECK constraints can reference only columns in the table where the constraint is defined.

The full SQL standard includes a type of constraint called an ASSERTION, which allows multi-table constraints, but MySQL does not implement this feature of SQL (very few brands of SQL databases do implement it).

CREATE TRIGGER t BEFORE INSERT ON DEPARTMENT
FOR EACH ROW BEGIN
  IF NEW.mgr_start_date < (SELECT bdate FROM EMPLOYEE WHERE ssn = NEW.mgr_ssn) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'manager is way too young';
  END IF;
END

Test:

insert into EMPLOYEE set ssn=123, bdate='2021-01-01';

insert into DEPARTMENT set mgr_ssn=123, dnumber='1', mgr_start_date='2010-01-01';

ERROR 1644 (45000): manager is way too young