test if a specific id is used as foreign key in any foreign table entry

i have a table 'users' with a column 'id'. this id is used as foreign key in many other tables (like posts, comments, etc.).

i want to find out if there are any links to a specific user's id in any foreign tables.

this gives me all table names and column names where the column 'id' from the table 'users' is used as foreign key:

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

now, how can i test if my specific user id is used as foreign key in one of those tables/columns in the result - and merge this test into the sql code above to get a single query?

i found a solution and added the following 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 foreig开发者_如何学运维n 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 can run the following test to determine if id 3 from 'users' is used as foreign key:

$db->isKeyUsedAsForeignKey('users',3)

As i already mentioned, it would be nice to have everything in one query. but as far as i understand it, this is not possible...

...or do you have any suggestions?

There is no way to do this in pure SQL. Many databases however do allow for the execution of "dynamic" queries using their procedural languages (like T-SQL, PL-SQL, etc). But this is really just another way of doing what you already did in php.

Another possibility, if you have good constraints for all of these foreign keys, and they don't cascade deletes, is to make a backup copy of the row, attempt to delete it, and catch the error that will occur if this a constraint violation. If there's no error, you have to restore the data though. Really just a theoretical idea, I think.

Finally, you could maintain an active reference count, or write a query in advance with knowledge of the foreign key relations.