Question 213465 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql check if any foreign keys entries in other tables are pointing to an entry in a table

lets say i have a table called employee that stores employee data

emp_id emp_data
1 xxx
2 xxx

Now lets say i want to delete an employee, if there exists any other entries of this employee’s Id pointing to this employee entry in the user table, i will get a foreign key exception when i try to delete said employee, which is good as we want to maintain database data integrity and ensure no employees that have been deleted have orphan entries in other tables.

The problem is there are a whole lot of tables that have a foreign key pointing to the employee table, and its extremely difficult to have to check one by one if lets say employee with id 1 exists in any entries in table a,b,c,d,e,…x

Running this command in mysql gets me all the table names that have foreign key relations to this tables primary key, ’emp_id’.

SELECT 
    TABLE_NAME, 
    COLUMN_NAME, 
    CONSTRAINT_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    REFERENCED_TABLE_NAME = 'employee';

so now im trying to figure out a way to make a query to check if any rows exist in any other tables with a foreign key pointing to the row im trying to delete. Is this even possible in MYSQL?

SELECT
    emps.* 
FROM
    employees emps
WHERE EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    WHERE kcu.REFERENCED_TABLE_NAME = 'employee'
    AND (
        (kcu.TABLE_NAME = emps.TABLE_NAME AND kcu.COLUMN_NAME =emps.emp_id
    )
);

this is what im trying so far? can someone tell me if this is even possible to do?

Tags:

2

Answers


  1. 0

    Yes, it is possible to check if any rows exist in other tables with a foreign key pointing to the employee row you want to delete.

    You can check this db-fiddle link: https://www.db-fiddle.com/f/ur2K7NU3HJzVWR28NNzANx/3

    I have created a sample and a stored procedure to dynamically check the table name. You can simply call the stored procedure CheckEmployeeReferences(emp.id) and replace emp.id with the employee ID you want to check.

    For example, you can call CALL CheckEmployeeReferences(1) to check if the employee ID’s foreign key exists in another table. After that, click "Run" to execute the query.

    • 1st Test. check employee with emp.id = 1, and the result will show reference_count = 2, This indicates that the emp.id Foreign Key is exist in another table enter image description here
    • 2st Test, check employee with emp.id = 4, and the result will show reference_count = 0, This indicates that the emp.id Foreign Key is not exist another table enter image description here

    This is just an example, so feel free to modify and customize it according to your needs. Hope it will helps you 🙂

    Login or Signup to reply.
  2. 0

    with respect for the opinions of friends, in my opinion:
    this error message "Unable to resolve column emps.TABLE_NAME" means that the SQL parser cannot find the TABLE_NAME column in the emps alias, as it’s not directly available in the employees table or the emps alias. To fix this issue, you need to use the correct syntax to access the table name. In most database systems, you can’t access the table name directly as a column, so you’ll need to adjust the query.

    If you want to check for relationships between the employees table and other tables in the database, you can modify the query like this:

    //***********************

    SELECT
    emps.*

    FROM

    employees emps

    WHERE EXISTS (

    SELECT 1

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu

WHERE kcu.REFERENCED_TABLE_NAME = 'employees' -- Change 'employee' to 'employees'
AND kcu.REFERENCED_COLUMN_NAME = 'emp_id'
AND kcu.TABLE_NAME = 'employees' -- Change 'emps.TABLE_NAME' to 'employees'
AND kcu.COLUMN_NAME = 'emp_id' -- Change 'emps.emp_id' to 'emp_id'

    );

    //***********************************

    By doing this, you are explicitly referencing the ’employees’ table in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE subquery, and the query should run without any issues.

    However, please keep in mind that this query will only return rows from the employees table if there are any relationships defined in the database where the employees.emp_id column is used as a foreign key in other tables. If no such relationships exist, the query will not return any results.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search