mysql查找所有外键_查询mysql所有外键约束

SELECT C.TABLE_SCHEMA 拥有者,

C.REFERENCED_TABLE_NAME 父表名称 ,

C.REFERENCED_COLUMN_NAME 父表字段 ,

C.TABLE_NAME 子表名称,

C.COLUMN_NAME 子表字段,

C.CONSTRAINT_NAME 约束名,

T.TABLE_COMMENT 表注释,

R.UPDATE_RULE 约束更新规则,

R.DELETE_RULE 约束删除规则

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C

JOIN INFORMATION_SCHEMA. TABLES T

ON T.TABLE_NAME = C.TABLE_NAME

JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R

ON R.TABLE_NAME = C.TABLE_NAME

AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME

AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME

WHERE C.REFERENCED_TABLE_NAME IS NOT NULL ;

结果：

外键约束创建的SQL：

ALTER TABLE course ADD CONSTRAINT FK_course_teacher FOREIGN KEY(Tid) REFERENCES teacher(Tid);

ALTER TABLE sc ADD CONSTRAINT FK_sc_student FOREIGN KEY(S) REFERENCES student(S);

ALTER TABLE sc ADD CONSTRAINT FK_sc_course FOREIGN KEY(C) REFERENCES course(Cid);

外键约束删除的SQL：

ALTER TABLE course DROP FOREIGN KEY Tid

附：添加唯一联合索引：

ALTER TABLE sc ADD UNIQUE INDEX idx_s_c(s,c)