how to find out the foreign keys and their reference table in mysql

   

        Simple query to find the foreign keys and their reference table and fields in Mysql
  

     SELECT * FROM information_schema.TABLE_CONSTRAINTS

WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'

AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema'

AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable';

in the place of "my schema" replace with your database name

and in "my table " enter the table for which you want to know the foreign key

2. To find the tables having auto increment fields the following query can be used

SELECT Table_name, IF(AUTO_INCREMENT,1,0) as Auto_increment

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'databasename' ;

3. To update tables having foreign key constrains it would be very difficult to drop the foreign keys and 

then update the tables for changes in that case we can use the following script for disabling and 

enabling foreign key's without dropping the foreign keys

set foreign_key_checks=0;// to disable keys

alter table queries............;//all alter queries should be written here

set foreign_key_checks=1;//to enable keys

this script should be saved as .sql file and that's it you are done....