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

2023年 4月 29日 30.9k 0

数据库中的某个表A,因为业务原因被移到别的库。麻烦的是,有几张子表(B, C, D等)建有指向它的外键,而且在创建时没有指定统一的外键名。如此一来,在不同的环境(开发、测试、生

数据库中的某个表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 SQLBEGIN  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 ;

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论