mysql找出外键等约束.docVIP

PAGE

PAGE1

mysql找出外键等约束

找出employee表的所有外键约束

select

TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME

fromINFORMATION_SCHEMA.KEY_COLUMN_USAGE

whereCONSTRAINT_SCHEMA=#39;vhr#39;AND

TABLE_NAME=#39;employee#39;;

Result：

此时如果想删除和posId相关的外键，只需要altertableemployeedropforeignkeyemployee_ibfk_3;

找出以employee为REFERENCED_TABLE的所有约束

select

TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME

fromINFORMATION_SCHEMA.KEY_COLUMN_USAGE

whereCONSTRAINT_SCHEMA=#39;vhr#39;AND

REFERENCED_TABLE_NAME=#39;employee#39;;

Result: