Return to Revisions

Your CHECK constraint can be much simpler:

ALTER TABLE billables
ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK (qb_id         IS NOT NULL OR
       xero_id       IS NOT NULL OR 
       freshbooks_id IS NOT NULL OR
       unleashed_id  IS NOT NULL OR
       csv_data      IS NOT NULL OR
       myob_id       IS NOT NULL) NOT VALID;

Or even just:

CONSTRAINT cc_at_least_one_mapping_needed_billables 
CHECK (NOT (qb_id,xero_id,freshbooks_id,unleashed_id,csv_data,myob_id) IS NULL) NOT VALID;

Why does that work?

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

I already added the NOT VALID clause that @a_horse mentioned. This way the cconstraint only applies to newly added rows. You also have to consider possible dump/restore cycles. Details:

• Disable all constraints and table checks while restoring a dump
• Best way to populate a new column in a large table?

And you can do it all in a single command, which is fastest and prevents possible concurrent transactions from doing anything wrong:

ALTER TABLE integrations.billables
  DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables
, ADD COLUMN myob_id varchar(255)
, ADD CONSTRAINT cc_at_least_one_mapping_needed_billables 
    CHECK (NOT (qb_id,xero_id, freshbooks_id,unleashed_id, csv_data, myob_id) IS NULL)
    NOT VALID;

Aside: If you already had the CHECK constraint on the same set of columns, just without the new myob_id, then there wouldn't be a problem, since every existing row would pass the new CHECK constraint with myob_id as well.