We created an SQL script responsible for cleaning the links in cont_cont, comp_comp and cont_comp, updating some custom fields in the meantime.
We also create the "opposite" link when missing or update it if already there.
We however noticed that we were getting some SQL errors stating that when creating the reverse link, the foreign key constraint was violated.
After some investigation, we found out that there were some records in cont_cont and comp_comp where k_contact2 and k_company2 respectively were pointing to non-existent contacts and companies (resp).
Truth is, there are no foreign key constraints on these k_...2 fields and therefore when someone deletes a record, only the links where its key is in k_contact (or k_company) are deleted, but not where its key is in k_contact2 (or k_company2), since no "delete cascade" is associated to them.
Is this the intended behaviour?
Is there a reason why the foreign key constraints on k_...2 fields are missing?