执行以下查询
select * from information_schema.key_column_usage
将显示数据库中存在的所有约束(列名,约束类型,表和模式)。你会注意到这些列:
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME
那么,如果你打算删除引用你的列的每个约束,你应该考虑REFERENCED_ *列,并运行如下:
DELETE FROM information_schema.key_column_usage
WHERE
REFERENCED_TABLE_SCHEMA='myschema'
AND
REFERENCED_TABLE_NAME='mytable'
AND
REFERENCED_COLUMN_NAME='mycolumn'