改写的两个mysql存储过程，用于判断主键外键

由于所在的数据库没有系统自带的sp_pkeys,sp_fkeys，所以改写了如下：

sp_pkeys

CREATE PROCEDURE `testdb`.`sp_pkeys` (IN table_name varchar(50),IN table_owner varchar(50),IN table_qualifier varchar(50)) 

 NOT DETERMINISTIC 
begin


/**procedure body**/
 select column_name from information_schema.columns where table_schema='testdb' and table_name=@table_name and column_key='PRI';


end;


SET @table_name='test';   
CALL sp_pkeys(@table_name,null,null);  
 

sp_fkeys

CREATE PROCEDURE `testdb`.`sp_fkeys` (IN pktb_name varchar(50),IN pkcolume_name varchar(50),IN pktb_owner varchar(50),IN pktb_qualifier varchar(50)) 
 NOT DETERMINISTIC 
 CONTAINS SQL 
 begin


/**procedure body**/
select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where table_name = pktb_name and COLUMN_NAME=pkcolume_name and REFERENCED_COLUMN_NAME is not null;
end; 


CALL sp_fkeys ('test',
'name',
null,

null)

other:

创建外键
alter table test add constraint FK_Id foreign key(Name) REFERENCES testfk(Name);

FK_Id  外键的名称

Name: test表中外键的字段名

后面那个Name:关联表的主键名

注意：如果test中存在name的值在testfk中没有会创建失败

删除外键

ALTER TABLE `test` DROP FOREIGN KEY `FK_ID`