MySQL - Foreign Keys on Foreign Keys

Somehow, up until now, I had never come across a situation where I even needed to consider setting a foreign key on a foreign key like the situation shown below: I wanted to see if it was possible, and if so, what implications it might have.

In the diagram I am trying to lay out the following rules. I have employees, of which only a subset are considered active. Of those active employees any number of them can have any number of accounts, but these accounts must be automatically removed when the employee is no longer considered active.

To test this, I ran the commands below on an Ubuntu 16.04 MySQL 5.7 database.

create database test;
use test;

CREATE TABLE `employee` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `active_employee` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` int unsigned NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `account` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` int unsigned NOT NULL,
    `username` varchar(255) NOT NULL,
    `password` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (employee_id) REFERENCES active_employee(employee_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee (name) VALUES ("john");

insert into active_employee (employee_id) VALUES (1);

insert into account (employee_id, username, password) VALUES (1, "john", "password123");
Copy to clipboard

Now I have set up all three tables and have values in each, proving that you can set up foreign key on a foreign key. What will happen if I delete the row from the secondary table? Will the account row be deleted, or will it remain because the id still exists in the employee table?

DELETE FROM `active_employee` WHERE `employee_id` = 1;
Copy to clipboard

It turns out, that this change does cascade down to the tertiary table as I had hoped. If you don't want this behavior and want the account to remain when an employee is no longer considered "active", then have the foreign key reference the employee table directly.