將外鍵約束添加到現有表列，引用新創建表中的列

假設我有一個表定義為

CREATE TABLE sales (
   agent_id   integer references agents(agent_id),
   sale_date  date,
   amout      numeric(10,2)
);

然後一個 ETL 過程用數據填充這個表

INSERT INTO sales VALUES
   (1, '2019-01-01', 1031.11),
   (1, '2019-01-02', 525.44),
   (1, '2019-01-03', 323.99);

但稍後，我需要添加一個包含實際處理日期的表格，例如

CREATE TABLE dates (
   date_idx   date primary key,
   year       integer not null,
   month      integer not null,
   day        integer not null
);

並想在舊表上添加外鍵約束：

ALTER TABLE sales
   ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
   REFERENCES dates (date_idx);

自然，我收到以下錯誤：

錯誤：在表“sales”上插入或更新違反了外鍵約束“sales_date_fk”詳細資訊：表“dates”中不存在鍵 (sale_date)=(2019-01-01)。

我知道我可以通過在添加約束之前刪除/截斷所有數據sales或預填充來解決此問題dates，但如果我可以避免它，我不希望這樣做。

有沒有辦法做到這一點？

我正在使用 Postgres 11，但是這個執行 9.6 的小提琴顯示了我在這裡試圖解釋的內容。

您可以在創建外鍵時使用NOT VALID修飾符來阻止它驗證現有數據。例如：

ALTER TABLE sales
ADD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)
REFERENCES dates (date_idx) NOT VALID;

我不會推薦這個，除非有什麼東西阻止你填充新的子表，所以你別無選擇。

這是 postgres 特定的語法，例如 SQL Server 中的等效語法是WITH NOCHECK，而其他一些可能根本不支持該選項，因此在嘗試支持多個數據庫後端時要小心。

從銷售表中選擇所有不同的日期並將數據插入到日期表中。將數據填充到該表中後，嘗試創建外鍵約束。

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