阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

191次阅读
没有评论

共计 2307 个字符,预计需要花费 6 分钟才能阅读完成。

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2307字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中