如何从MySQL数据库中获取所有约束的列表?
使用information_schema.table_constraints
表获取每个表定义的约束的名称:
select *
from information_schema.table_constraints
where constraint_schema = 'YOUR_DB'
使用information_schema.key_column_usage
表格获取那些约束中的字段:
select *
from information_schema.key_column_usage
where constraint_schema = 'YOUR_DB'
如果你说的是外键约束,那么请使用information_schema.referential_constraints
:
select *
from information_schema.referential_constraints
where constraint_schema = 'YOUR_DB'
information_schema.columns.column_default
中。 - Bill Karwin@Senseful给出了很好的答案。
对于那些只想要约束名称列表(而不是其他细节/列)的人,我提供修改后的查询:
SELECT DISTINCT(constraint_name)
FROM information_schema.table_constraints
WHERE constraint_schema = 'YOUR_DB'
ORDER BY constraint_name ASC;
如果您想查看主键和外键约束以及围绕这些约束的规则,例如ON_UPDATE和ON_DELETE,以及列和外部列名称的所有信息,则这真的很有帮助:
这非常有助于查看主键、外键约束以及这些约束周围的规则,例如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'
您甚至可以添加一些有关这些列的进一步信息,只需将其添加到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
select * from information_schema.table_constraints where CONSTRAINT_NAME like 'YOUR_TABLE%';
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
查询表名为"tabnam"的用户约束条件。