删除数据库中未指定名称的外键的存储过程

数据库中的某个表A，因为业务原因被移到别的库。麻烦的是，有几张子表(B, C, D等)建有指向它的外键，而且在创建时没有指定统一的外键名。如此一来，在不同的环境（开发、测试、生产等）中该外键的名称不一样，必须逐个去查询外键名再进行删除，十分不便。

为此，特地编写了一个存储过程，只须指定子表名（B,C,D）和外键的列名，直接调用该存储过程即可。

Oracle的存储过程代码如下：

-- 删除指定表、指定列上的外键（系统命名或未知名） CREATE OR REPLACE PROCEDURE DROP_FK(P_TABLE IN VARCHAR2, P_COLUMN IN VARCHAR2) AS   v_fk varchar2(100);   v_sql varchar2(1000); BEGIN   SELECT A.CONSTRAINT_NAME INTO v_fk FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B   WHERE  A.TABLE_NAME=UPPER(P_TABLE) AND A.COLUMN_NAME=UPPER(P_COLUMN)     AND B.TABLE_NAME=UPPER(P_TABLE) AND B.CONSTRAINT_TYPE='R'     AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME;   v_sql := 'ALTER TABLE '|| P_TABLE ||' DROP CONSTRAINT '|| v_fk;   EXECUTE IMMEDIATE v_sql; EXCEPTION   WHEN OTHERS THEN     DBMS_OUTPUT.PUT_LINE('发生错误'||SQLCODE||'：'||SQLERRM); END DROP_FK; /

PostgreSql的代码如下：

-- 删除指定表、指定列上的外键（系统命名或未知名） CREATE OR REPLACE FUNCTION DROP_FK(P_TABLE IN VARCHAR, P_COLUMN IN VARCHAR) RETURNS void AS $$   DECLARE v_fk  VARCHAR(100);   DECLARE v_sql VARCHAR(1000); BEGIN   SELECT tc.constraint_name INTO v_fk   FROM information_schema.table_constraints AS tc, information_schema.key_column_usage AS kcu   WHERE tc.table_name = lower(P_TABLE) AND kcu.column_name=lower(P_COLUMN)     AND constraint_type = 'FOREIGN KEY' AND tc.constraint_name = kcu.constraint_name;   v_sql := 'ALTER TABLE '|| P_TABLE ||' DROP CONSTRAINT '|| v_fk;   EXECUTE v_sql; EXCEPTION   WHEN OTHERS THEN     RAISE EXCEPTION '(%)', SQLERRM; END; $$ LANGUAGE plpgsql;

MySQL的代码如下：

DELIMITER //

DROP PROCEDURE IF EXISTS drop_fk//

-- 删除指定表、指定列上的外键（系统命名或未知名） CREATE PROCEDURE drop_fk(IN P_TABLE VARCHAR(100), IN P_COLUMN VARCHAR(100)) LANGUAGE SQL BEGIN   DECLARE v_fk  VARCHAR(100);   DECLARE v_sql VARCHAR(1000);   DECLARE v_cnt INT;   SELECT count(*) INTO v_cnt   FROM information_schema.table_constraints TC, information_schema.key_column_usage TCU   WHERE TC.TABLE_NAME=LOWER(P_TABLE) AND TCU.TABLE_NAME=LOWER(P_TABLE)       AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND COLUMN_NAME=LOWER(P_COLUMN)       AND TC.CONSTRAINT_NAME=TCU.CONSTRAINT_NAME;   IF v_cnt=1 THEN       SELECT TC.CONSTRAINT_NAME INTO v_fk       FROM information_schema.table_constraints TC, information_schema.key_column_usage TCU       WHERE TC.TABLE_NAME=LOWER(P_TABLE) AND TCU.TABLE_NAME=LOWER(P_TABLE)         AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND COLUMN_NAME=LOWER(P_COLUMN)         AND TC.CONSTRAINT_NAME=TCU.CONSTRAINT_NAME;       SET v_sql = CONCAT('ALTER TABLE ', P_TABLE, ' DROP FOREIGN KEY ', v_fk);       SET @sql = v_sql;       PREPARE stmt FROM @sql;       EXECUTE stmt;       DEALLOCATE PREPARE  stmt;   END IF; END; //

DELIMITER ;