Mysql show create restriction?

Question

Is there an easy way to query a table for its constraints (especially for additional keys) like show create table, but only for constraints?

thanks,

pvgoddijn

Answer 1

To show only foreign key restrictions, you can check the constraint_type parameter in the schema information files .table_constraints and get the columns affected in the key_key.key_column_usage_information file through the connection

SELECT b.table_name, b.column_name, b.constraint_name, b.referenced_table_name, b.referenced_column_name FROM information_schema.table_constraints a JOIN information_schema.key_column_usage b ON a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name WHERE a.table_schema=database() AND a.constraint_type='FOREIGN KEY' ORDER BY b.table_name, b.constraint_name; 

Answer 2

 select * from information_schema.KEY_COLUMN_USAGE where table_schema = <db_name> and table_name = <table_name>; 

Answer 3

 SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'; 

Foreign key constraints are listed in the Comment column of the output file.

Answer 4

MySQL 5.1 Manual

 SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';