Tuesday 3 December 2019

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:
 Employee sample database with tables dependenciesImage 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(4NOT NULL,
  `name` varchar(40NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `department_employee` (
  `emp_id` int(11NOT NULL,
  `dept_id` char(4NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `department_manager` (
  `emp_id` int(11NOT NULL,
  `dept_id` char(4NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(11NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(20NOT NULL,
  `last_name` varchar(20NOT NULL,
  `gender` varchar(10NOT NULL,
  `hire_date` date NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `payment` (
  `emp_id` int(11NOT NULL,
  `salary` int(11NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `title` (
  `emp_id` int(11NOT NULL,
  `title` varchar(50NOT 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:

Employee table dependencies
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:

Department table dependenciesTable 2. Department table dependencies

0 comments:

Post a Comment