i've got a table 'users' having a column 'id'. this id can be used as foreign type in a number of other tables (like posts, comments, etc.).

i wish to discover if you will find any links to some specific user's id in almost any foreign tables.

this provides me all table names and column names in which the column 'id' in the table 'users' can be used as foreign key:

SELECT table_name,column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'users';

now, how do i test if my specific user id can be used as foreign type in among individuals tables/posts within the result - and merge this test in to the sql code above to obtain a single query?

i discovered an answer and added the next function to my php database class:

 * isKeyUsedAsForeignKey
 * returns true if the $key from column 'id' in $table is used as foreign key in other tables and there are one or more entries in one or more of this foreign tables with $key from $table in the respective foreign key column
 * @param string $table     table name
 * @param int $key          entry id
 * @return bool
public function isKeyUsedAsForeignKey( $table, $key ) {
    $key = intval($key);
    if( preg_match('/^[-_a-z0-9]+$/i',$table) && $key>0 ) {
        $result = $this->query("SELECT table_name,column_name FROM information_schema.key_column_usage WHERE referenced_table_name = '".$table."'");
        $select = array();
        while( $result && $row=$result->fetch_assoc() )
            array_push($select,"(SELECT COUNT(DISTINCT ".$row['column_name'].") FROM ".$row['table_name']." WHERE ".$row['column_name']."='".$key."') AS ".$row['table_name']);
        $result2 = $this->query("SELECT ".implode(',',$select));
        if( $result2 && $row=$result2->fetch_row() )
            return array_sum($row)>0 ? true : false;
    return false;

Now i'm able to run the next test to find out if id 3 from 'users' can be used as foreign key:


When i already pointed out, it might be nice to possess my way through one query. but so far as i realize it, no chance...

...or have you got any suggestions?

There's no method of doing this in pure SQL. Many databases however do permit the execution of "dynamic" queries utilizing their procedural languages (like T-SQL, PL-SQL, etc). But this is actually yet another method of doing that which you already did in php.

Another possibility, for those who have good constraints its these foreign secrets, plus they don't cascade removes, is to create a backup copy from the row, make an effort to remove it, and catch the mistake which will occur if the a constraint breach. If there is no error, you need to restore the information though. Really only a theoretical idea, I believe.

Finally, you can maintain an energetic reference count, or write a question ahead of time with understanding from the foreign key relations.