1

Let's say the "user_id" field on "table_users" is primary key for 10 foreign keys on 10 other tables. I want to rename the field of primary key on "table_users", but it doesn't allow me to rename it because there are 10 foreign keys from 10 other tables are linked to it. May I know how to know which of the 10 tables has the foreign keys? What is the sql code to show the 10 tables which has the foreign keys?

I am using phpMyAdmin and InnoDB engine. I know SHOW ENGINE INNODB STATUS can do the job but it show me 1 table for each time, mean I need to repeat rename the primary key field for 10 times and run SHOW ENGINE INNODB STATUS for 10 times to find out all the 10 tables. Is there any other better solution to find out the 10 tables?

zac1987
  • 2,721
  • 9
  • 45
  • 61

2 Answers2

2

You can try querying INFORMATION_SCHEMA database:

select ku.* 
from INFORMATION_SCHEMA.table_constraints tc   
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA =   
ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME =     
'your_table_name'
a1ex07
  • 36,826
  • 12
  • 90
  • 103
0

As the mysql root user, you can get around this issue using

SET foreign_key_checks = 0;

This disables the constraint system and will allow you to alter your table definition. Of course from there all your foreign key constraints will be broken, and need to be dropped and recreated.

SET foreign_key_checks = 1;

Will turn the constraint system back on.

Mysql also has a data dictionary that can be queried. That was covered fully in This previous question so I'm not going to repeat any of that info.

Community
  • 1
  • 1
gview
  • 14,876
  • 3
  • 46
  • 51
  • I have tried your suggestion. I click Database_name > Table_Users > SQL > `SET foreign_key_checks = 0;`. Then I rename the primary field (user_id) on Table_Users. It still not allow me to rename it. Why? The link u show me return error "URL you typed no longer exists." – zac1987 Sep 18 '11 at 18:21