Check if an element (primary key) is involved somewhere else in the database

user_8275210 0
Viewed 23

I would like to check if an element is implicated in another database table as a foreign key, not knowing which table it is involved in.
For now I have only managed to find out which are the tables where the element stands as a foreign key, using the following query:

SELECT TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS 
WHERE REFERENCED_TABLE_NAME = 'funzione_operativa_sedi_e_recapiti';

The table is made up as follows:

CREATE TABLE IF NOT EXISTS funzione_operativa_sedi_e_recapiti (
   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   nome VARCHAR(255) NOT NULL UNIQUE,
   descrizione VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Summarizing, I would like my query to show me only the tables where that item exists as a foreign key. If the element is not in those tables (for example those tables are empty), there is no need for me to retrieve them.

Thanks in advance.

mariadb mysql sql
1 Answers

The following query will work:

SELECT 
  table_name, column_name     
FROM
  information_schema.key_column_usage
WHERE
  referenced_table_name = <table name>
  and referenced_column_name = <desired column>
user_122912770
Related