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?
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 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: