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
<http://forum.kinghost.co.nz/memberlist.php?mode=viewprofile&u=2&sid=e999f8370385657a65d41d5ff60b0b38>
- DigitalOcean <https://m.do.co/c/a0d31c597a03>
