0

Suppose I have an ActiveCustomers table with some FKs (eg CountryID), and I also have a NonActiveCustomers with the same FKs. When I insert a new line to ActiveCustomers - the system must check the "1" side (from 1:N) to verify the CountryID (or whatever) exists. When I move a line from ActiveCustomers to NonActiveCustomers - I don't need the system to verify it again because it has already been done when the line was originally inserted into ActiveCustomers. In this case I can create a Check FK constraint on ActiveCustomers, and a NoCheck FK constraint on NonActiveCustomers; but the problem is that when I delete lines from the "1" side table (Countries), I want the system to check in both "N" tables that the operation is allowed: I don't want to have orphaned lines there. How can I do that? The Nocheck disables all validations in the inserts to NonActiveCustomers and in the deletions & updates (in Countries), even when I specify explicitly On Delete No Action On Update No Action. Of course, I can create a Trigger on Countries in order to verify that the Delete or Update are correct, but is there a way to do that using the constraint mechanism?

Geri Reshef
  • 397
  • 1
  • 6
  • 17
  • What is the `NoCheck` aspect doing for you if you're not wanting orphans to exist? – Damien_The_Unbeliever Jan 06 '15 at 07:08
  • Damien- I'm not sure I understood your question.. Anyway: when I insert a line from ActiveCustomers to NonActiveCustomers - I don't want the system to check the constraint, its redundsnt; but when I delete From Countries I want the system to verify it wouldn't convert lines in NonActiveCustomers into orphands. – Geri Reshef Jan 06 '15 at 12:12
  • But would it actually cause you any **problems** if the constraint was checked? It seems that you're trying to achieve some perceived efficiency by removing the check - but I'm not sure whether that's your only purpose and whether you've actually verified that it will provide any benefit to you. So I was wondering if there was some other reason you wanted to remove the check. – Damien_The_Unbeliever Jan 06 '15 at 13:11
  • We had some waits problems with an historical data table with 6 FKs. Those FKs are responsible for no more of 10% of the load, however I was curioused about it from a technical perspective, and I guess the NoCheck disables all checks. Thank you! – Geri Reshef Jan 07 '15 at 06:12

0 Answers0