How to find the dependencies of a MySQL table

In this article, we are going to learn how to find the dependencies of a table in MySQL. For this, we are going to use the structure of an Employee database as an example.

First, we have to create the database.The Employee database we are going to use has the following structure:

Image 1. Employee sample database with tables dependencies

As we can see, there are tables like Employee or Department that have some dependencies. Let’s execute the following script to create the “employees_database” with that structure: 

CREATE DATABASE IF NOT EXISTS `employees_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `employees_database`; CREATE TABLE `department` (   `id` char(4) NOT NULL,   `name` varchar(40) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `department_employee` (   `emp_id` int(11) NOT NULL,   `dept_id` char(4) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `department_manager` (   `emp_id` int(11) NOT NULL,   `dept_id` char(4) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `employee` (   `id` int(11) NOT NULL,   `birth_date` date NOT NULL,   `first_name` varchar(20) NOT NULL,   `last_name` varchar(20) NOT NULL,   `gender` varchar(10) NOT NULL,   `hire_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `payment` (   `emp_id` int(11) NOT NULL,   `salary` int(11) NOT NULL,   `from_date` date NOT NULL,   `to_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `title` (   `emp_id` int(11) NOT NULL,   `title` varchar(50) NOT NULL,   `from_date` date NOT NULL,   `to_date` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `department`   ADD PRIMARY KEY (`id`),   ADD UNIQUE KEY `dept_name` (`name`); ALTER TABLE `department_employee`   ADD PRIMARY KEY (`emp_id`,`dept_id`),   ADD KEY `dept_id` (`dept_id`); ALTER TABLE `department_manager`   ADD PRIMARY KEY (`emp_id`,`dept_id`),   ADD KEY `dept_id` (`dept_id`); ALTER TABLE `employee`   ADD PRIMARY KEY (`id`); ALTER TABLE `payment`   ADD PRIMARY KEY (`emp_id`,`from_date`); ALTER TABLE `title`   ADD PRIMARY KEY (`emp_id`,`title`,`from_date`); ALTER TABLE `department_employee`   ADD CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE,   ADD CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE; ALTER TABLE `department_manager`   ADD CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE,   ADD CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`) ON DELETE CASCADE; ALTER TABLE `payment`   ADD CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE; ALTER TABLE `title`   ADD CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE;

Script 1. Employee sample database creation

Finding MySQL table dependencies

Let’s build a query that returns the dependencies of the Employee table. To achieve our objective, we will rely on the table INFORMATION_SCHEMA.KEY_COLUMN_USAGE, which is where MySQL stores the foreign keys data.

SELECT TABLE_NAME as `DEPENDENCY`, COLUMN_NAME as `COLUMN`, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'employee';

Script 2. Query to find Employee table dependencies

Result:

Table 1. Employee table dependencies

We can use the same query if we want to find the Department table dependencies. We only have to change the WHERE clause to filter the results by Department. 

SELECT TABLE_NAME as `DEPENDENCY`, COLUMN_NAME as `COLUMN`, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'department';

Script 4. Query to find Department table dependencies

Result:

Table 2. Department table dependencies