錯誤：某些行違反了檢查約束

我有以下錯誤：

ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row

詢問：

ALTER TABLE integrations.tax_aggregates
DROP COLUMN IF EXISTS myob_id,
ADD COLUMN myob_id integrations.FOREIGN_IDENTIFIER;
COMMENT ON COLUMN integrations.tax_aggregates.myob_id IS 'Foreign key for MYOB';

ALTER TABLE integrations.tax_aggregates DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed,
ADD CONSTRAINT cc_at_least_one_mapping_needed CHECK ((("qb_id" IS NOT NULL) :: INTEGER +
                                                     ("xero_id" IS NOT NULL) :: INTEGER +
                                                     ("freshbooks_id" IS NOT NULL) :: INTEGER +
                                                     ("myob_id" IS NOT NULL) :: INTEGER +
                                                     ("ppy_id" IS NOT NULL) :: INTEGER) > 0);

                                                     DROP INDEX IF EXISTS integrations.ix_tax_aggregates_myob_ids_ids;
CREATE INDEX ix_tax_aggregates_myob_ids_ids ON integrations.tax_aggregates USING BTREE (myob_id)
 WHERE myob_id IS NOT NULL;

我究竟做錯了什麼？我該如何解決這個問題？

更新：

詢問：

ALTER TABLE integrations.accounts
DROP COLUMN IF EXISTS myob_settings,
ADD COLUMN myob_settings JSON;

ALTER TABLE integrations.accounts DROP CONSTRAINT IF EXISTS cc_at_least_one_setting_needed,
   ADD CONSTRAINT cc_at_least_one_setting_needed CHECK (("qb_settings" IS NOT NULL) or
                                                         ("xero_settings" IS NOT NULL) or
                                                         ("freshbooks_settings" IS NOT NULL) or
                                                         ("myob_settings" IS NOT NULL) or
                                                         ("ppy_settings" IS NOT NULL));

選擇結果：

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL AND xero_settings IS NOT NULL and freshbooks_settings IS NOT NULL AND myob_settings IS NOT NULL AND ppy_settings IS NOT NULL

它返回 0 行

---

SELECT * FROM integrations.accounts WHERE qb_settings IS NULL OR xero_settings IS NULL OR freshbooks_settings IS NULL OR myob_settings IS NULL OR ppy_settings IS NULL;

返回（59 行）：

"account_id"|"global_settings"|"qb_settings"|"xero_settings"|"freshbooks_settings"|"myob_settings"|"ppy_settings"

"30374"|""|"{""id"":""1412494720"",""active"":true,""settings"":{""secret"":""iisCCNSiH4jmRArWUPi0rxa9LntQdAA3kc7lbLtf"",""token"":""qyprdigrVpUijVKDxJ72iBZrnh4tvlwMA1QuVxxyPSgLIz4j"",""expires"":""2016-01-31 04:34:37""},""plan"":""QuickBooks Online Essentials""}"|""|""|""|""


ERROR:  check constraint "cc_at_least_one_setting_needed" is violated by some row

您有違反 CHECK CONSTRAINT 的 NULL。

要驗證這確實是問題所在，請執行此 SQL。

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR            
xero_settings IS NOT NULL OR... (fill in the fields that correspond to 
those in the CONSTRAINT).

這將為您提供所有具有違反約束的 NULL 值的欄位的記錄。

從您的 SQL 中，您有 59 條這樣的記錄 - 這就是您收到錯誤的原因。

您需要做的是糾正此問題，即執行類似於以下內容的 SQL：

UPDATE My_Table SET My_Field = 0 WHERE My_Field IS NULL.

顯然，您需要確保設置的值與系統的業務邏輯和其他系統要求相對應。

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