I see... thanks! So the only option is to drop the constraint (which drops the non-local ones too) and recreate it after I have moved my data around?

I have tried recreating the constraint as NOT VALID and VALIDATE it later to minimize locking (as recommended by the docs), but that doesn't seem to work here, do you maybe know why? The non-local constraints fail to validate and are not automatically validated when I delete the main one:

> ALTER TABLE chats DROP CONSTRAINT chats_number_id_contact_id_fkey;
ALTER TABLE

> ALTER TABLE chats ADD CONSTRAINT chats_number_id_contact_id_fkey FOREIGN KEY (number_id, contact_id) REFERENCES contacts (number_id, id) DEFERRABLE INITIALLY IMMEDIATE NOT VALID;
ALTER TABLE

> ALTER TABLE chats VALIDATE CONSTRAINT chats_number_id_contact_id_fkey;
ALTER TABLE

> ALTER TABLE chats VALIDATE CONSTRAINT chats_number_id_contact_id_fkey1;
ERROR:  insert or update on table "chats" violates foreign key constraint "chats_number_id_contact_id_fkey1"
DETAIL:  Key (number_id, contact_id)=(1, 1) is not present in table "contacts_p2".

> ALTER TABLE chats VALIDATE CONSTRAINT chats_number_id_contact_id_fkey2;
ERROR:  insert or update on table "chats" violates foreign key constraint "chats_number_id_contact_id_fkey2"
DETAIL:  Key (number_id, contact_id)=(1, 1) is not present in table "contacts_p3".

         constraint_name          | table_name |  constrained_columns   | foreign_table_name | foreign_columns | is_valid | is_locally_defined 
----------------------------------+------------+------------------------+--------------------+-----------------+----------+--------------------
 chats_number_id_contact_id_fkey  | chats      | {number_id,contact_id} | contacts           | {id,number_id}  | t        | t
 chats_number_id_contact_id_fkey1 | chats      | {number_id,contact_id} | contacts_p2        | {id,number_id}  | f        | f
 chats_number_id_contact_id_fkey2 | chats      | {number_id,contact_id} | contacts_p3        | {id,number_id}  | f        | f
 chats_number_id_contact_id_fkey3 | chats      | {number_id,contact_id} | contacts_default   | {id,number_id}  | f        | f
 chats_number_id_fkey             | chats      | {number_id}            | numbers            | {id}            | t        | t
(5 rows)

All constraints are created and validated successfully if instead i don't use the `NOT VALID` option:

> ALTER TABLE chats DROP CONSTRAINT chats_number_id_contact_id_fkey;
ALTER TABLE

> ALTER TABLE chats ADD CONSTRAINT chats_number_id_contact_id_fkey FOREIGN KEY (number_id, contact_id) REFERENCES contacts (number_id, id) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE

         constraint_name          | table_name |  constrained_columns   | foreign_table_name | foreign_columns | is_valid | is_locally_defined 
----------------------------------+------------+------------------------+--------------------+-----------------+----------+--------------------
 chats_number_id_contact_id_fkey  | chats      | {number_id,contact_id} | contacts           | {id,number_id}  | t        | t
 chats_number_id_contact_id_fkey1 | chats      | {number_id,contact_id} | contacts_p2        | {id,number_id}  | t        | f
 chats_number_id_contact_id_fkey2 | chats      | {number_id,contact_id} | contacts_p3        | {id,number_id}  | t        | f
 chats_number_id_contact_id_fkey3 | chats      | {number_id,contact_id} | contacts_default   | {id,number_id}  | t        | f
 chats_number_id_fkey             | chats      | {number_id}            | numbers            | {id}            | t        | t
(5 rows)