ERROR: check constraint is violated by some row

I've got the following error:

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

Query:

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;

What am I doing wrong? How can I solve the problem?

UPDATED:

Query:

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 results:

```
SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL AND xero_settings IS NOT NULL a

You can negate your constraint to find out the rows that does not satisfy it:

SELECT * 
FROM integrations.accounts
WHERE NOT ((("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)))

This can be simplified as:

WHERE
      NOT NOT((("qb_settings" IS NULL) and
            ("xero_settings" IS NULL) and
            ("freshbooks_settings" IS NULL) and
            ("myob_settings" IS NULL) and
            ("ppy_settings" IS NULL)))

and further as:

WHERE 
          "qb_settings" IS NULL and
          "xero_settings" IS NULL and
          "freshbooks_settings" IS NULL and
          "myob_settings" IS NULL and
          "ppy_settings" IS NULL

Note that the rule for constraints is that they must not evaluate to False, Null satisfies a constraint, so if there is a possibility that it can evaluate to Null (not in this case) this has to be taken into consideration.

Judging from your comments you are interested in leaving historical rows as is, and only validate new rows. Two options I can think of:

-
Add a temporal dimension to the table (when the row was created, say CREATE_TIME) and change the constraint to something like:

ALTER TABLE integrations.accounts
     ADD CONSTRAINT cc_at_least_one_setting_needed 
         CHECK ( (CREATE_TIME <= t_0) or 
                 ((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)) );

Where t_0 is the time from which the rows should be validated. In other words, it's the implication: (CREATE_TIME > t_0) =>

-
Create a trigger instead of a check constraint. Triggers are fired when something happens so they don't care about rows that already exists. Something like:

CREATE FUNCTION validate_row() 
RETURNS trigger AS $validate_row$
BEGIN
    -- Check that data is valid
    IF NEW.qb_id IS NULL AND xero_id IS NULL AND ... THEN
        RAISE EXCEPTION 'empname cannot be null';
    END IF;
END;
$validate_row$ LANGUAGE plpgsql;

CREATE TRIGGER validate_row 
    BEFORE INSERT OR UPDATE ON integrations.accounts
        FOR EACH ROW EXECUTE PROCEDURE validate_row();

Both ideas untested.

StackExchange Database Administrators Q#127163, answer score: 6