對具有 CHECK 約束的列集強制執行 NOT NULL 僅對新行

我有一個表，需要添加一個沒有預設值的新列：

約束：

ALTER TABLE integrations.billables 
DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables,
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
   ("xero_id" IS NOT NULL) :: INTEGER +
   ("freshbooks_id" IS NOT NULL) :: INTEGER +
   ("unleashed_id" IS NOT NULL) :: INTEGER +
   ("csv_data" IS NOT NULL) :: INTEGER +
   ("myob_id" IS NOT NULL) :: INTEGER) > 0);

柱子：

ALTER TABLE integrations.billables
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id varchar(255);

題：

如何為下一個值添加約束，而不是為那些已經存在的值添加約束？（否則我會得到錯誤檢查約束“”被某行違反）。

這與我之前的問題有關：錯誤：某些行違反了檢查約束

將所有現有行標記為舊行：

ALTER TABLE integrations.billables
ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false;

UPDATE integrations.billables SET is_old = true;

並設置約束以忽略舊行：

ALTER TABLE integrations.billables
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK (
   NOT(("qb_id", "xero_id", "freshbooks_id", "unleashed_id", "csv_data", "myob_id") IS NULL)
   OR is_old
);

（是的，該IS NULL檢查有效。請參見此處。）

這種機制的優點：

• 約束仍然有效
• 您可以繼續更新舊行而不填寫此值

缺點：

• 未來類似的情況會很混亂。您必須為boolean第二個新列添加第二列或其他一些環跳。
• 如果你想強制更新的行被賦予一個值，這不會這樣做。
• 這有可能被濫用，因為有人可以將is_old標誌翻轉為true. （不過，這可以解決。見下文。）如果最終使用者不能直接訪問數據庫並且您可以相信開發人員不會對數據做古怪的事情，這不是要擔心的事情。

如果您擔心有人更改標誌，您可以設置觸發器以防止任何插入或更新設置is_old為true：

CREATE FUNCTION throw_error_on_illegal_old()
 RETURNS trigger
 LANGUAGE plpgsql
 AS $$
 BEGIN
   IF NEW.is_old THEN
     -- Need to make sure we don't try to access
     -- OLD in an INSERT
     IF TG_OP = 'INSERT' THEN
       RAISE 'Cannot create new with is_old = true';
     ELSE
       IF NOT OLD.is_old THEN
         RAISE 'Cannot change is_old from false to true';
       END IF;
     END IF;
   END IF;
   -- If we get here, all tests passed
   RETURN NEW;
 END
 $$
;

CREATE TRIGGER billables_prohibit_marking_row_old
BEFORE INSERT OR UPDATE ON integrations.billables
FOR EACH ROW EXECUTE PROCEDURE throw_error_on_illegal_old()
;

您仍然必須相信，沒有人可以修改數據庫模式並放棄您的觸發器或其他東西，但如果他們要這樣做，他們也可以放棄約束。

這是一個SQLFiddle 展示。請注意，“應該跳過”行不在輸出中（如我們所願）。

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