在 Postgres 中創建 FK 時刪除可能的孤兒

我正在研究一個複制模式和數據的腳本。我首先創建所有結構，除了foreign keys從源文件讀取它之後system tables，然後複製數據，然後創建 FK，以便在我複制父項後將一個可能的孤兒插入子表中（當我執行腳本時） )，即使 FK 創建由於孤立數據而失敗，我仍然可以獲得基本架構和數據。

我的下一步是做一些事情，比如將try/catchcreate FK 語句包裹起來，如果它失敗，刪除導致它的孤立數據。這樣做會有些複雜，因為我必須提取所有引用鍵並組裝我自己的刪除語句（我目前不這樣做，因為 PG 系統表提供了一個REFERENCES我可以連接的就緒語句）。

但我想知道是否有一種方法可以為ALTER TABLE x ADD CONSTRAINT fk_y FOREIGN KEY ... DELETE ANY WOULD-BE ORPHANS我節省這一步。

所以如果我的父表是：

parent_id     | parent_name
---------------------------------
1             | Joe
2             | Mary

孩子是：

child_id      | child_name     | parent_parent_id
--------------------------------------------------
11           | Jimmy          | 1
21           | Joey           | 2
31           | Jeff           | 3

‘Jeff’ 將在強制引用完整性之前被刪除，因為他的父級和他在源父級複製到目標之後被引入源。然後可以根據一致的數據創建 FK。

---

澄清（來自評論/聊天討論）：

您是否在單個事務中讀取數據？

• 我不使用單筆交易，因為它太大了。我按表分解檢索到的數據，並按表段（5000 或 10000 行的批次）對一些大表進行分解。
• 這本質上是一個 ETL，它從 DB 中生成一個“邪惡的雙胞胎”。希望這是有道理的。
• 平均而言，我的環境中的數據庫副本需要 20 分鐘（對於小型數據庫）到 1.5-2 小時（對於較大的數據庫），有些表有數百萬行。我在 Python 腳本的記憶體中逐塊載入

pg_dump或呢pg_basebackup？

• 我的應用程序做了一些其他pg_dump不做的事情，例如更好的日誌記錄。此外，pg_dump 期望在源和目標以及許多其他問題上使用相同的使用者。我試過了，不喜歡。我更喜歡我的工具，我想解決這個僅偶爾發生的小問題。

正如評論中所建議的那樣，標準工具pg_dump可能會更好地為您服務。它會拍攝一致的快照並在備份期間自動保留參照完整性 - 假設源數據庫是一致的（FK 約束到位）。如果可以，請使用它！

還提到了相關工具pg_dumpall和pg_basebackup ，但它們適用於整個數據庫集群，而您顯然只想複製（部分）單個數據庫。

但是，這個問題可能仍然有正當理由。拒絕投機似乎是不公平的。這個問題本身就很有趣：

如何將不一致的數據導入 FK 關係並修復它？

NOT VALID選項

創建 FK 約束沒有DELETE ANY WOULD-BE ORPHANS選項（如您所願），但Postgres 9.1 或更高版本***NOT VALID***中約束的相關選項應該是有用的。手冊：FOREIGN KEYALTER TABLE

ADD table_constraint [ NOT VALID ]

這種形式使用與 相同的語法向表添加新約束 CREATE TABLE，加上選項NOT VALID，目前僅允許用於外鍵和 CHECK 約束。如果約束標記為NOT VALID，則跳過可能很長的初始檢查以驗證表中的所有行是否滿足約束。約束仍將針對後續的插入或更新強制執行（也就是說，除非引用表中存在匹配行，否則它們將失敗，在外鍵的情況下；除非新行與指定的檢查匹配，否則它們將失敗約束）。但是數據庫不會假定約束適用於表中的所有行，直到使用該VALIDATE CONSTRAINT選項對其進行驗證。

因此，您可以創建相關表並導入數據並創建 PK 約束和 FK 約束NOT VALID。

ALTER TABLE child ADD CONSTRAINT child_parent_id_fkey
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) **NOT VALID**;

這可以防止任何新的寫入違反參照完整性，同時容忍現有的違反。（並且系統知道這一點。）

儘早刪除孤兒：

DELETE FROM child c
WHERE  NOT EXISTS (
  SELECT 1 FROM parent p
  WHERE  p.parent_id = c.parent_id
  );

如果數據是一致的，這將無濟於事，並且需要以任何一種方式完成工作以驗證完整性。因此，對於需要驗證的不可靠數據，它不會變得更有效率。

然後驗證約束：

ALTER TABLE child VALIDATE CONSTRAINT child_parent_id_fkey;

不必在同一個事務中。閱讀手冊了解詳情。

有關的：

• 部分數據庫轉儲/恢復
• 恢復轉儲時禁用所有約束和表檢查

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