Skip to main content
Database Administrators

Return to Revisions

5 of 14
deleted 193 characters in body
user avatar
user avatar

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

It returns 0 rows

ERROR:

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