database – 如何删除具有外键依赖项的重复行？

我确信这是常见的地方,但谷歌没有帮助.我试图在PostgreSQL 9.1中编写一个简单的存储过程,它将从父cpt表中删除重复的条目.父表cpt由子表实验室引用,定义为：

CREATE TABLE lab (
 recid serial NOT NULL,
 cpt_recid integer,
  ........
 CONSTRAINT cs_cpt FOREIGN KEY (cpt_recid)
   REFERENCES cpt (recid) MATCH SIMPLE
   ON UPDATE NO ACTION ON DELETE RESTRICT,
 ...
);

我遇到的最大问题是如何获取失败的记录,以便我可以在EXCEPTION子句中将它用于将子行从lab移动到一个可接受的键,然后循环返回并从cpt表中删除不必要的记录.

这是(非常错误的)代码：

CREATE OR REPLACE FUNCTION h_RemoveDuplicateCPT()
  RETURNS void AS
$BODY$
BEGIN
LOOP
   BEGIN

   DELETE FROM cpt
   WHERE recid IN (
      SELECT recid
      FROM  (
         SELECT recid,
         row_number() over (partition BY cdesc ORDER BY recid) AS rnum
         FROM cpt) t
      WHERE t.rnum > 1)
   RETURNING recid;

   IF count = 0 THEN
      RETURN;
   END IF;  

   EXCEPTION WHEN foreign_key_violation THEN
      RAISE NOTICE 'fixing unique_violation';
      RAISE NOTICE 'recid is %' , recid;
   END;
END LOOP;
END;                    
$BODY$
LANGUAGE plpgsql VOLATILE;

使用 data-modifying CTEs的单个SQL语句可以更有效地执行此操作.

WITH plan AS (
   SELECT *
   FROM  (
      SELECT recid, min(recid) OVER (PARTITION BY cdesc) AS master_recid
      FROM   cpt
      ) sub
   WHERE  recid <> master_recid  -- ... <> self
   )
 , upd_lab AS (
   UPDATE lab l
   SET    cpt_recid = p.master_recid   -- link to master recid ...
   FROM   plan p
   WHERE  l.cpt_recid = p.recid
   )
DELETE FROM cpt c
USING  plan p
WHERE  c.recid = p.recid
RETURNING c.recid;

db<>小提琴here(第11页)
SQL Fiddle(第9.6页)

这应该更快更清洁.循环比较昂贵,异常处理相对更昂贵.
更重要的是,实验室中的引用会自动重定向到cpt中的相应主行,但这不在原始代码中.所以你可以一次删除所有欺骗.

如果愿意,您仍然可以将它包装在plpgsql或SQL函数中.

说明

>在第一个CTE计划中,使用相同的cdesc标识每个分区中的主行.在你的情况下,recid最小的行.
>在第二个CTE upd_lab中,将引用欺骗的所有行重定向到cpt中的主行.
>最后,删除dupes,这不会引发异常,因为依赖行实际上同时链接到剩余的主行.

ON DELETE RESTRICT

所有CTE和语句的主要查询几乎同时在基础表的相同快照上运行.他们没有看到彼此对基础表的影响：

> Delete parent if it’s not referenced by any other child

人们可能期望使用ON DELETE RESTRICT的FK约束来引发异常,因为[per documentation] [3]：

Referential actions other than the NO ACTION check cannot be deferred,
even if the constraint is declared deferrable.

但是,上面的语句是单个命令,[再次手册] [3]：

A constraint that is not deferrable will be checked immediately after
every command.

大胆强调我的.当然,也适用于限制较少的默认值ON DELETE NO ACTION.

但要警惕写入相同表的并发事务,但这是一个普遍的考虑因素,并非特定于此任务.

一个例外适用于UNIQUE和PRIMARY KEY约束,但这不涉及这种情况：

> Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?