更新引用時刪除行

假設我有一個person帶有 id 和其他一些列的表。其他表使用 引用該person表ON UPDATE CASCADE。例如：

CREATE TABLE person (id int PRIMARY KEY);
CREATE TABLE sale (id int PRIMARY KEY,
   person_id int REFERENCES person(id) ON UPDATE CASCADE);

現在，我要做的是：從 person 表中刪除重複的條目，修復引用salereferences。例如，使用此數據：

INSERT INTO person values (1), (2), (3);
INSERT INTO sale VALUES (11, 1), (12, 2), (13, 3);

Person1和2實際上是相同的，所以它們應該被“合併”。通過合併，我的意思是2在更新表中人員的引用時1排除人員sale。

由於表上有一個引用人員，我想知道是否有某種方法可以在刪除人員的同時導致級聯更新ON UPDATE CASCADE，而無需手動更新表sale``2

我在這裡試圖實現的是避免使用專門的過程來更新引用，因為引用表的數量很可能會增加，需要在這個過程中進行維護（而且已經相當大了）。

不，沒有用於將一個鍵合併到另一個鍵的內置功能，自動更新所有引用行。您可能需要解決各種並發症。

簡單案例

您需要UPDATE在所有引用表和DELETE被引用表上執行。簡單的情況如下所示：

BEGIN;

--  1. update references
UPDATE sale
SET    person_id = _org_id
WHERE  person_id = _dupe_id;

-- 2. kill dupe 
DELETE FROM person
WHERE  id = _dupe_id;

COMMIT;

但可能有各種…

並發症

想像一個表在 和person_tag之間實現 n:m 關係，person並且tag在(person_id, tag_id). 您不能UPDATE使用會導致重複標籤的行。您需要解決衝突：僅更新與唯一約束不衝突的行並刪除其餘行。

-- example for conflict resolution
UPDATE person_tag pt
SET    person_id = _org_id
WHERE  person_id = _dupe_id
AND NOT EXISTS (
  SELECT 1 FROM person_tag
  WHERE  person_id = _org_id
  AND    tag_id = pt.tag_id
  );

DELETE FROM person_tag
WHERE  person_id = _dupe_id;
-- end person_tag

可能還有更複雜的星座…

全自動化

如果您有許多可以以相同方式（或不斷變化的數字）處理的引用表，您可以使用動態 SQL自動執行該過程。

對於具有單列 fk 約束且沒有並發症的簡單情況：

CREATE OR REPLACE FUNCTION f_merge_id(_tbl regclass, _col text, _org_id int, _dupe_id int)
 RETURNS void AS
$func$
DECLARE
  rec record;
BEGIN
  FOR rec IN  -- find all referencing columns
     SELECT c.conrelid::regclass AS tbl, quote_ident(a2.attname) AS col
     FROM   pg_catalog.pg_attribute  a1
     JOIN   pg_catalog.pg_constraint c  ON c.confrelid = a1.attrelid
                                       AND c.confkey   = ARRAY[a1.attnum]
     JOIN   pg_catalog.pg_attribute  a2 ON a2.attrelid = c.conrelid
                                       AND a2.attnum   = c.conkey[1]
     WHERE  a1.attrelid = _tbl
     AND    a1.attname  = _col
     AND    c.contype   = 'f'  -- fk constraint
  LOOP        -- Redirect to _org_id all references to dupe_id
     EXECUTE format('
        UPDATE %1$s
        SET    %2$s = $1
        WHERE  %2$s = $2'
       ,rec.tbl, rec.col)
     USING _org_id, _dupe_id;
  END LOOP;

  -- Finally kill (now orphaned) dupe 
  EXECUTE format('DELETE FROM %s WHERE %s = $1', _tbl, _col)
  USING _dupe_id;
END
$func$ LANGUAGE plpgsql;

稱呼：

SELECT f_merge_id('person', 'person_id', _org_id := 1, _dupe_id := 2);

這將更新具有引用主表的外鍵的任意數量的表。

在呼叫中使用命名參數，這不是必需的，但您需要確保 不要在此處混淆原始和重複。

SQL小提琴。

要點

• 如果對plpgsql函式中動態SQL的概念不熟悉，可以參考一些相關問題：

  • https://dba.stackexchange.com/questions/tagged/postgresql+dynamic-sql+plpgsql

• 正確引用列名以避免 SQL 注入。quote_ident()為您做到這一點：

  • Postgres 函式中的 SQL 注入與準備好的查詢

• 如果您必須處理繁重的並發負載，您必須準備好最終DELETE可能會失敗，這將回滾整個事務。並發事務可以使用_dupe_id. 有多種方法可以解決這個問題，這超出了這個問題的範圍。

引用自：https://dba.stackexchange.com/questions/76035