找出employee表的所有外鍵約束 Result: 此時如果想刪除和posId相關的外鍵,只需要 找出以employee為REFERENCED_TABLE的所有約束 Result: ...
找出employee表的所有外鍵約束
select
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_SCHEMA ='vhr' AND
TABLE_NAME = 'employee';
Result:
此時如果想刪除和posId相關的外鍵,只需要 alter table employee drop foreign key employee_ibfk_3;
找出以employee為REFERENCED_TABLE的所有約束
select
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_SCHEMA ='vhr' AND
REFERENCED_TABLE_NAME = 'employee';
Result: