Mysql显示创建约束？ [英] Mysql show create constraint?

问题描述

有没有简单的方法来查询一个表的约束（foreignkeys specificaly）
喜欢显示创建表，但仅限于约束？

谢谢，

pvgoddijn

解决方案

可以检查information_schema.table_constraints中的constraint_type，并通过连接获取information_schema.key_column_usage中的受影响的列。b

pre $ c $ SELECT b.table_name，b .column_name，b.constraint_name，
b.referenced_table_name，b.referenced_column_name $ b $ 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;

Is there a easy way to query a table for its constraints(foreignkeys specificaly) like show create table, but for the constraints only?

thanks,

pvgoddijn

解决方案

To show only the foreign key constraints you can check the constraint_type in information_schema.table_constraints and get the affected columns in information_schema.key_column_usage via a join

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;

这篇关于Mysql显示创建约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！