0 votes

Hi all,

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?

Thank you,
Kind regards,

Laurent

asked in Database by (134 points)
edited by

1 Answer

+2 votes
Best answer

Your database, is that SQL Server? I assume so, because in Oracle that should not happen.

If I remember well, back in the days of SQL Server 2000 it was technically impossible to add this constraint (Robert told me once)

I suggest you create a case about it, it seems completely unlogic that this limitation will still exist in recent versions of SQL Server.

answered by (7.4k points)
selected by
1,249 questions
1,519 answers
1,859 comments
328 users