Show constraints on tables command

I use

SHOW CREATE TABLE mytable;

This shows you the SQL statement necessary to receate mytable in its current form. You can see all the columns and their types (like DESC) but it also shows you constraint information (and table type, charset, etc.).

Simply query the INFORMATION_SCHEMA:

USE INFORMATION_SCHEMA;SELECT TABLE_NAME,       COLUMN_NAME,       CONSTRAINT_NAME,       REFERENCED_TABLE_NAME,       REFERENCED_COLUMN_NAMEFROM KEY_COLUMN_USAGEWHERE TABLE_SCHEMA = "<your_database_name>"       AND TABLE_NAME = "<your_table_name>"       AND REFERENCED_COLUMN_NAME IS NOT NULL;

The main problem with the validated answer is you'll have to parse the output to get the informations. Here is a query allowing you to get them in a more usable manner :

SELECT cols.TABLE_NAME, cols.COLUMN_NAME, cols.ORDINAL_POSITION,cols.COLUMN_DEFAULT, cols.IS_NULLABLE, cols.DATA_TYPE,    cols.CHARACTER_MAXIMUM_LENGTH, cols.CHARACTER_OCTET_LENGTH,    cols.NUMERIC_PRECISION, cols.NUMERIC_SCALE,    cols.COLUMN_TYPE, cols.COLUMN_KEY, cols.EXTRA,    cols.COLUMN_COMMENT, refs.REFERENCED_TABLE_NAME, refs.REFERENCED_COLUMN_NAME,    cRefs.UPDATE_RULE, cRefs.DELETE_RULE,    links.TABLE_NAME, links.COLUMN_NAME,    cLinks.UPDATE_RULE, cLinks.DELETE_RULEFROM INFORMATION_SCHEMA.`COLUMNS` as colsLEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS refsON refs.TABLE_SCHEMA=cols.TABLE_SCHEMA    AND refs.REFERENCED_TABLE_SCHEMA=cols.TABLE_SCHEMA    AND refs.TABLE_NAME=cols.TABLE_NAME    AND refs.COLUMN_NAME=cols.COLUMN_NAMELEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cRefsON cRefs.CONSTRAINT_SCHEMA=cols.TABLE_SCHEMA    AND cRefs.CONSTRAINT_NAME=refs.CONSTRAINT_NAMELEFT JOIN INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` AS linksON links.TABLE_SCHEMA=cols.TABLE_SCHEMA    AND links.REFERENCED_TABLE_SCHEMA=cols.TABLE_SCHEMA    AND links.REFERENCED_TABLE_NAME=cols.TABLE_NAME    AND links.REFERENCED_COLUMN_NAME=cols.COLUMN_NAMELEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS cLinksON cLinks.CONSTRAINT_SCHEMA=cols.TABLE_SCHEMA    AND cLinks.CONSTRAINT_NAME=links.CONSTRAINT_NAMEWHERE cols.TABLE_SCHEMA=DATABASE()    AND cols.TABLE_NAME="table"