ERROR: check constraint is violated by some row

Question

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 and freshbooks_settings IS NOT NULL AND myob_settings IS NOT NULL AND ppy_settings IS NOT NULL

It returns 0 rows

---

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;

RETURN (59 rows):

"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

Answer 1

You have NULLs that violate the CHECK CONSTRAINT.

To verify that this is indeed the problem, run this 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).

This will give you all the records that have fields which have a NULL value which violate the CONSTRAINT.

From your SQL, you have 59 such records - that's why you're getting the error.

What you need to do to rectify this is to run SQL similar to the following:

UPDATE My_Table SET My_Field = 0 WHERE My_Field IS NULL.

Obviously it is up to you to ensure that the values that you set correspond to your system's business logic and other system requirements.

Answer 2

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:

1. 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) => <your contraint>

2. 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.

Answer 3

There are some row(s) that violate the constraint. But the 59 rows are not very relevant (although it includes the offending rows) because it's a different, bigger result set. It's the rows that have at least one of the 5 values as NULL. You want the ones that only have all 5 values as NULL.

To find them, you can run the "opposite" of your constraint:

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

which will show all rows that have all the 5 columns as NULL. You could also write it as follows:

SELECT *
FROM integrations.accounts 
WHERE ( "qb_id" IS NULL  
      AND "xero_id" IS NULL 
      AND "freshbooks_id" IS NULL
      AND "myob_id" IS NULL 
      AND "ppy_id" IS NULL 
      ) ;

Answer 4

Many variants have been posted, but the simplest check to find violating rows that have NULL in every column of the set is:

SELECT * FROM integrations.accounts 
WHERE (qb_id,xero_id,freshbooks_id,myob_id,ppy_id) IS NULL;

The WHERE expression evaluates to TRUE if and only if every single column IS NULL. Details:

• NOT NULL constraint over a set of columns
• Why is IS NOT NULL false when checking a row type?