数据库中的某个表 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 ;
来源: http://www.linuxidc.com/Linux/2019-07/159601.htm