How to determine which columns were set in CASCADE in mysql

Question

I am curious to find out if there is to indicate which columns were set in CASCADE using mysql. Executing DESC TABLE; seems to give nothing. Any suggestions?

Answer 1

Starting with MySQL 5.1.10, you can get this information from the INFORMATION_SCHEMA tables by joining REFERENTIAL_CONSTRAINTS until KEY_COLUMN_USAGE :

 SELECT kcu.COLUMN_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME) WHERE 'CASCADE' IN (rc.DELETE_RULE, rc.UPDATE_RULE) AND kcu.TABLE_SCHEMA = DATABASE() AND kcu.TABLE_NAME = 'my_table' 

Answer 2

You can use:

 show create table tableName; 

which displays a create table containing links to cascading deletes.

Example related to docs :

 mysql> SHOW CREATE TABLE ibtest11c\G *************************** 1. row *************************** Table: ibtest11c Create Table: CREATE TABLE `ibtest11c` ( `A` int(11) NOT NULL auto_increment, `D` int(11) NOT NULL default '0', `B` varchar(200) NOT NULL default '', `C` varchar(175) default NULL, PRIMARY KEY (`A`,`D`,`B`), KEY `B` (`B`,`C`), KEY `C` (`C`), CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11a` (`A`, `D`) ON DELETE CASCADE ON UPDATE CASCADE, // <--- This is what you are looking for CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`) REFERENCES `ibtest11a` (`B`, `C`) // <--- This is what you are looking for ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=INNODB CHARSET=latin1 1 row in set (0.01 sec)