MySQL - Unique Key Across Tables

As part of another problem, I wanted to see if it was possible to ensure that a key was only in one of two tables. This is shown in the example below where I want an employee to be either in the ActiveEmployee table, or the TerminatedEmployee table, but never both.

One could just use a single employees table with a nullable termination_date to identify whether an employee is active or terminated for this scenario. However this structure is in preparation for other relationships in future that are beyond the scope of this tutorial.

Table Setup

You can use the commands below to set up the tables as shown above.

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 `terminated_employee` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `employee_id` int unsigned NOT NULL,
    `termination_date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Enforcing Employee In One of Two Tables

Now how do I prevent the same employee being referenced in both the active and terminated tables? We can use triggers to prevent insertions on either table if the value is in the other.

This trigger will prevent an employee being inserted into the active table if they are already in the terminated one.

DELIMITER $$

CREATE TRIGGER unique_active_employee BEFORE INSERT ON active_employee
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM terminated_employee WHERE employee_id = NEW.employee_id;
  IF (c > 0) THEN
    signal sqlstate '45000';
  END IF;

END$$
DELIMITER ;

This trigger will prevent an employee being inserted into the terminated table if they are already in the active one.

DELIMITER $$

CREATE TRIGGER unique_terminated_employee BEFORE INSERT ON terminated_employee
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM active_employee WHERE employee_id = NEW.employee_id;
  IF (c > 0) THEN
    signal sqlstate '45000';
  END IF;

END$$
DELIMITER ;

Now lets test this by creating an employee called John, and try to add him to both the active and terminated tables.

# Create an active employee called John.
INSERT INTO `employee` (`name`) VALUES ("john");
INSERT into `active_employee` (`employee_id`) VALUES (1);

# Erroneous attempt to insert the same employee into the terminated table.
INSERT into terminated_employee (`employee_id`, `termination_date`) VALUES (1, '2017-01-01');

After the last command, our triggers will kick in to protect the database state. You will be shown the following error:

ERROR 1644 (45000): Unhandled user-defined exception condition

Firing John

If we want to fire John, we need to remove him from the active table before inserting him into the terminated table with the commands below:

START TRANSACTION;
DELETE FROM `active_employee` WHERE `employee_id`=1;
INSERT INTO `terminated_employee` (`employee_id`, `termination_date`) VALUES (1, '2017-01-01');
COMMIT;

Appendix

If you don't want to use signal for your triggers, you could achieve the same effect by setting the value to null as shown below, but this would create confusing error messages to the user.

DELIMITER $$

CREATE TRIGGER unique_active_employee BEFORE INSERT ON active_employee
FOR EACH ROW BEGIN
  DECLARE c INT;
  SELECT COUNT(*) INTO c FROM terminated_employee WHERE employee_id = NEW.employee_id;
  IF (c > 0) THEN
    SET NEW.employee_id = NULL;
  END IF;

END$$
DELIMITER ;

The error message you would get would be:

ERROR 1048 (23000): Column 'employee_id' cannot be null

This would likely cause confusion for other users as they would see that they had provided a non-null value and might not realize a trigger was making it null.

References

• Stack Overflow - MySQL Trigger to prevent INSERT under certain conditions