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

db<>fiddle here
_{Old sqlfiddle}

Aside 1: 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.

Aside 2: In some RDBMS it makes sense to use varchar(255) to optimize performance. This is irrelevant to Postgres and 255 as length modifier only makes sense if you actually need to restrict the length to a maximum of 255:

• Do fixed-width rows improve PostgreSQL read performance?
• Any downsides of using data type “text” for storing strings?