Postgres - 不能刪除約束，因為對象依賴它，即使它是重複的

在將我的 Postgres 從 10.7 升級到 13.6 後，我遇到了一種情況。我有幾年來建立數據庫歷史的飛行路線腳本。對模式的更改圍繞 PK 進行了洗牌，並且在這樣做的同時必須創建一些約束。

V1__PLM.sql

create table artifacts_registry (
   artifact_id varchar(36) not null,
   artifact_name varchar(255) not null,
   ...);
alter table artifacts_registry 
   add constraint UK_t5rk0b5yybufi8pkkxato2e11 unique (artifact_name);

V17__PLM.sql

alter table artifacts_registry
   add column p_artifact_name varchar(255);

V19___PLM.sql

alter table artifacts_registry
   add constraint artifacts_registry_pkey primary key (artifact_name);

此時我注意到我在單個列上有一個重複的索引 - artifacts_registry_pkey 和 uk_t5rk0b5yybufi8pkkxato2e11。

我的 flyway 腳本試圖放棄這個額外的約束。

V78__PLM.sql

ALTER TABLE artifacts_registry 
   DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11;

這在新的 Postgres 13.6 版本上失敗了。

Migration V78__PLM.sql failed
-----------------------------
SQL State  : 2BP01
Error Code : 0
Message    : ERROR: cannot drop constraint uk_t5rk0b5yybufi8pkkxato2e11 on table artifacts_registry because other objects depend on it
 Detail: constraint fkhg85thdq13hf91f7h6mbd5dza on table artifacts_registry depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk1afmjb153f2dgxs4roj2pzez9 on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fkafh3xt7w1oyg7sz2r6c7jo8nk on table artifacts_relation depends on index uk_t5rk0b5yybufi8pkkxato2e11
constraint fk89pg0m2cqgnoidk3uxuxex76x on table execution_artifacts depends on index uk_t5rk0b5yybufi8pkkxato2e11
 Hint: Use DROP ... CASCADE to drop the dependent objects too.
Location   : db/migration/schema/V78__.sql (/Users/dobrim1/dev/P-PLM/target/classes/db/migration/schema/V78__PLM.sql)
Line       : 1
Statement  : ALTER TABLE artifacts_registry DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11

令人困惑的是，這一切都用於傳遞以前版本的 Postgres 和 AWS Aurora 10.7、11.x 和 12.x。

它試圖給我使用 DROP CASCADE 的提示。但是，我真的不想這樣做，因為這些索引很有用並且引用了有效的關係。這裡的問題是 Postgres 有兩個相同的唯一約束，但它選擇 uk_t5rk0b5yybufi8pkkxato2e11 作為“主要”約束，它正在執行刪除約束。

問題

如何將 p_artifact_name 索引標記為主索引，以便刪除 uk_t5rk0b5yybufi8pkkxato2e11？

相關資訊——

• 這些更改已針對 AWS Aurora 13.6 部署在生產環境中。每次容器啟動時，它們都會驗證遷移歷史記錄並且沒有任何問題。所以生產伺服器並沒有像 Postgres 在本地那樣選擇主索引。
• 當我啟動 Postgres 13.6 容器並啟動我的應用程序時，該問題在本地出現，然後它必須從 V1__PLM.sql 開始執行完整的遷移歷史記錄。
• 由於付費性質，基線和撤消遷移不是一種選擇。

PostgreSQL 的實現方式，外鍵引用目標表上的唯一索引，而不是列列表。如果您在單個列上有多個唯一約束，則引用該列的外鍵約束將引用這些唯一索引之一。因此，如果您首先創建了唯一約束，這就是外鍵將指向的內容。

正確的解決方案是將外鍵與唯一約束一起刪除並再次創建它，以便它指向主鍵。您可以將其創建為NOT VALID並稍後對其進行驗證，以避免長時間持有強鎖。

您還可以更新pg_constraint條目並修改conindid，但您還必須修改相應的pg_depend條目以更改依賴關係。但是，不支持修改目錄表，這樣很容易破壞你的數據庫，所以我不推薦這種做法。

事實上，解決這個問題的唯一好方法似乎是通過添加CASCADE到 SQL 來放棄約束。然後您必須添加回刪除的約束。當遷移執行時，flyway 將看到一些約束被刪除並記錄。

DB: drop cascades to 4 other objects

因此，請確保您添加了相同的約束。將約束添加回後，它們將與剩餘的正確約束相關聯。

所有這些都可以在之前失敗的同一個遷移腳本中完成。

V78__PLM.sql

ALTER TABLE artifacts_registry DROP CONSTRAINT uk_t5rk0b5yybufi8pkkxato2e11 CASCADE;

-- add back constraints dropped by CASCADE operation above
ALTER TABLE artifacts_registry ADD CONSTRAINT fkhg85thdq13hf91f7h6mbd5dza ...
ALTER TABLE artifacts_relation ADD CONSTRAINT fk1afmjb153f2dgxs4roj2pzez9 ...
ALTER TABLE artifacts_relation ADD CONSTRAINT fkafh3xt7w1oyg7sz2r6c7jo8nk ...
ALTER TABLE execution_artifacts ADD CONSTRAINT fk89pg0m2cqgnoidk3uxuxex76x ...

但是當然，使用這種方法，您將不得不修復此版本的 flyway 校驗和。

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