ERROR: check constraint - PostgreSQL 9.2

["drum.lucas@xxxxxxxxx" <drum.lucas@xxxxxxxxx>]

Hi all, need some help to add a constraint to an existing table (with data).

I'm running the command:

Query:

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));

But, I got the following error:

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

So, I made a SELECT to get some data, to see where's the issue:

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;

And I got 59 rows. So, it seems that's my problem - I have NULLS that violate the CHECK CONSTRAINT.

Question:

How can I solve the problem? How can I get the command successfully be done?

Cheers;

Lucas Possamai

- kinghost.co.nz

- DigitalOcean