MySQL 数据库中的约束列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3024981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List of Constraints from MySQL Database
提问by lolajl
How do I get a list of all constraints from a particular database?
如何从特定数据库中获取所有约束的列表？
回答by Senseful
Use the information_schema.table_constraintstable to get the names of the constraints defined on each table:
使用该information_schema.table_constraints表获取每个表上定义的约束的名称：
select *
from information_schema.table_constraints
where constraint_schema = 'YOUR_DB'
Use the information_schema.key_column_usagetable to get the fields in each one of those constraints:
使用该information_schema.key_column_usage表获取每个约束中的字段：
select *
from information_schema.key_column_usage
where constraint_schema = 'YOUR_DB'
If instead you are talking about foreign key constraints, use information_schema.referential_constraints:
相反，如果您在谈论外键约束，请使用information_schema.referential_constraints：
select *
from information_schema.referential_constraints
where constraint_schema = 'YOUR_DB'
回答by Harshith J.V.
Great answer by @Senseful.
@Senseful 的精彩回答。
I am presenting modified query for those who are only looking for list of constraint names (and not other details/columns):
我正在为那些只查找约束名称列表（而不是其他详细信息/列）的人提供修改后的查询：
SELECT DISTINCT(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_schema = 'YOUR_DB'
ORDER BY constraint_name ASC;
回答by friek108
This really helps if you want to see primary and foreign key constraints as well as rules around those constraints such as ON_UPDATE and ON_DELETE and the column and foreign column names all together:
如果您想查看主键和外键约束以及围绕这些约束的规则（例如 ON_UPDATE 和 ON_DELETE 以及列名和外键名），这真的很有帮助：
SELECT tc.constraint_schema,tc.constraint_name,tc.table_name,tc.constraint_type,kcu.table_name,kcu.column_name,kcu.referenced_table_name,kcu.referenced_column_name,rc.update_rule,rc.delete_rule
FROM information_schema.table_constraints tc
inner JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
AND tc.table_name = kcu.table_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
AND tc.table_name = rc.table_name
WHERE tc.constraint_schema = 'my_db_name'
You may even want to add in some further information about those columns, simply add this into the SQL (and select the columns you want):
您甚至可能想要添加有关这些列的更多信息，只需将其添加到 SQL 中（并选择您想要的列）：
LEFT JOIN information_schema.COLUMNS c
ON kcu.constraint_schema = c.table_schema
AND kcu.table_name = c.table_name
AND kcu.column_name = c.column_name
回答by EmEsEn
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
