2

I was trying to create a table via SSMS 11.0.6020.0 using SQL Server, via the table designer. I have defined the primary key, the columns and foreign keys. All was well, but when I have hit Ctrl+s to save the script, I received an error message stating that On Delete Cascade may cause cycles and change the remove behavior. I have followed the instructions given and changed the on delete and on update to do nothing for the nullable column used as foreign key, but I wonder what is the problem with on delete cascade and on update cascade for nullable foreign key.

My question is related to this one, but as far as I know it is not a duplicate, since I have a single reference to a given table and the problem was that it was nullable and the other question is about multiple references. The error message is similar though.

I believe that if that column is null for a record, then it does not have a reference in the referenced table, therefore there is no reference which could trigger delete/update on that record. However, if there is a reference, that could act as if the column was not nullable in that case. Is there an objective reason for this behavior (like performance, indexing, etc.)? I can simply not see the possible cycles when a nullable column is used as a foreign key, as the null values should be immune to changes at the reference table according to my opinion.

Community
  • 1
  • 1
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Why on earth would you ever use cascade delete to begin with? – HLGEM Sep 08 '16 at 17:32
  • I would like to make sure that whenever a referenced record is removed, the record having a foreign key pointing to that record is removed as well. – Lajos Arpad Sep 08 '16 at 17:34
  • But cascade delete is a very poor practice. If you ended up with 10,000,000 child records it could cause a huge lock up of your system. It is not permitted by many dbas for this reason. – HLGEM Sep 08 '16 at 17:36
  • 1
    I understand your point and it is valid in many cases, but I am not so strict about this specific issue, I think about my schema and the nature of the data I will store along with potential problems. I am not worried about huge system locks regarding this specific data. – Lajos Arpad Sep 08 '16 at 17:58

0 Answers0