title: test if a specific id is used as foreign key in any foreign table entry
body: <p>i have a table 'users' with a column 'id'.
  this id is used as foreign key in many other tables (like posts, comments, etc.).</p>
  
  <p>i want to find out if there are any links to a specific user's id in any foreign tables.</p>
  
  <p>this gives me all table names and column names where the column 'id' from the table 'users' is used as foreign key:</p>
  
  <pre><code>SELECT table_name,column_name
  FROM information_schema.key_column_usage
  WHERE referenced_table_name = 'users';
  </code></pre>
  
  <p><strong>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?</strong></p>
  
  <p>i found a solution and added the following function to my php database class:</p>
  
  <pre><code>/**
   * 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) &amp;&amp; $key&gt;0 ) {
          $result = $this-&gt;query("SELECT table_name,column_name FROM information_schema.key_column_usage WHERE referenced_table_name = '".$table."'");
          $select = array();
          while( $result &amp;&amp; $row=$result-&gt;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-&gt;query("SELECT ".implode(',',$select));
          if( $result2 &amp;&amp; $row=$result2-&gt;fetch_row() )
              return array_sum($row)&gt;0 ? true : false;
      }
      return false;
  }
  </code></pre>
  
  <p>Now i can run the following test to determine if id 3 from 'users' is used as foreign key:</p>
  
  <pre><code>$db-&gt;isKeyUsedAsForeignKey('users',3)
  </code></pre>
  
  <p>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...</p>
  
  <p>...or do you have any suggestions?</p>