SQL Server On Delete Event
I've looked around a lot for the answer to this question, but I'm having trouble trying to implement cascade delete conditions between two particular tables in a database model I'm working. When I go to deploy, SQL Server keeps throwing an error saying that there might be cycles or multiple cascade paths.
Consider a table foo that has a primary key row of an auto incrementing int.
fooid|Column1|Column2...
------------------
1
2
Consider next a table bar that (among other things) has two columns which each contain foreign key references back to the id in foo.
barId|fooRef1|Stuff|fooRef2
---------------------------
I'm trying to implement a cascade delete operation between foo and bar so that if a row in foo gets deleted, any rows in bar that contain that id (in either column) get deleted too. There's a check constraint that the fooRef values cannot be the same in a given row. When I try to implement the cascade delete with "ON DELETE CASCADE" in both foreign key rows, I get that same deployment error about cycles and multiple cascade paths. I tried then to implement a trigger on the delete event for foo, where on delete of a row, it would delete all rows in other table with the foreign keys set to null. However in implementing this, when I then delete the check constraint and "ON DELETE SET NULL" for both rows in bar, sql still gives me that error.
I'm aware that the most easy fix for this would be change the model so that I only had one foreign key reference to the other table, but that's not really doable. What else could I do to fix this?
sql-server database cascade edited Nov 21 '18 at 20:15
marc_s
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
add a comment |
I've looked around a lot for the answer to this question, but I'm having trouble trying to implement cascade delete conditions between two particular tables in a database model I'm working. When I go to deploy, SQL Server keeps throwing an error saying that there might be cycles or multiple cascade paths.
Consider a table foo that has a primary key row of an auto incrementing int.
fooid|Column1|Column2...
------------------
1
2
Consider next a table bar that (among other things) has two columns which each contain foreign key references back to the id in foo.
barId|fooRef1|Stuff|fooRef2
---------------------------
I'm trying to implement a cascade delete operation between foo and bar so that if a row in foo gets deleted, any rows in bar that contain that id (in either column) get deleted too. There's a check constraint that the fooRef values cannot be the same in a given row. When I try to implement the cascade delete with "ON DELETE CASCADE" in both foreign key rows, I get that same deployment error about cycles and multiple cascade paths. I tried then to implement a trigger on the delete event for foo, where on delete of a row, it would delete all rows in other table with the foreign keys set to null. However in implementing this, when I then delete the check constraint and "ON DELETE SET NULL" for both rows in bar, sql still gives me that error.
I'm aware that the most easy fix for this would be change the model so that I only had one foreign key reference to the other table, but that's not really doable. What else could I do to fix this?
sql-server database cascade edited Nov 21 '18 at 20:15
marc_s
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03
add a comment |
I've looked around a lot for the answer to this question, but I'm having trouble trying to implement cascade delete conditions between two particular tables in a database model I'm working. When I go to deploy, SQL Server keeps throwing an error saying that there might be cycles or multiple cascade paths.
Consider a table foo that has a primary key row of an auto incrementing int.
fooid|Column1|Column2...
------------------
1
2
Consider next a table bar that (among other things) has two columns which each contain foreign key references back to the id in foo.
barId|fooRef1|Stuff|fooRef2
---------------------------
I'm trying to implement a cascade delete operation between foo and bar so that if a row in foo gets deleted, any rows in bar that contain that id (in either column) get deleted too. There's a check constraint that the fooRef values cannot be the same in a given row. When I try to implement the cascade delete with "ON DELETE CASCADE" in both foreign key rows, I get that same deployment error about cycles and multiple cascade paths. I tried then to implement a trigger on the delete event for foo, where on delete of a row, it would delete all rows in other table with the foreign keys set to null. However in implementing this, when I then delete the check constraint and "ON DELETE SET NULL" for both rows in bar, sql still gives me that error.
I'm aware that the most easy fix for this would be change the model so that I only had one foreign key reference to the other table, but that's not really doable. What else could I do to fix this?
sql-server database cascade edited Nov 21 '18 at 20:15
marc_s
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
I've looked around a lot for the answer to this question, but I'm having trouble trying to implement cascade delete conditions between two particular tables in a database model I'm working. When I go to deploy, SQL Server keeps throwing an error saying that there might be cycles or multiple cascade paths.
Consider a table foo that has a primary key row of an auto incrementing int.
fooid|Column1|Column2...
------------------
1
2
Consider next a table bar that (among other things) has two columns which each contain foreign key references back to the id in foo.
barId|fooRef1|Stuff|fooRef2
---------------------------
I'm trying to implement a cascade delete operation between foo and bar so that if a row in foo gets deleted, any rows in bar that contain that id (in either column) get deleted too. There's a check constraint that the fooRef values cannot be the same in a given row. When I try to implement the cascade delete with "ON DELETE CASCADE" in both foreign key rows, I get that same deployment error about cycles and multiple cascade paths. I tried then to implement a trigger on the delete event for foo, where on delete of a row, it would delete all rows in other table with the foreign keys set to null. However in implementing this, when I then delete the check constraint and "ON DELETE SET NULL" for both rows in bar, sql still gives me that error.
I'm aware that the most easy fix for this would be change the model so that I only had one foreign key reference to the other table, but that's not really doable. What else could I do to fix this?
sql-server database cascade sql-server database cascade edited Nov 21 '18 at 20:15
marc_s
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
edited Nov 21 '18 at 20:15
marc_s
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
edited Nov 21 '18 at 20:15
marc_s
582k13011241269
edited Nov 21 '18 at 20:15
marc_s
582k13011241269
edited Nov 21 '18 at 20:15
marc_s
582k13011241269
582k13011241269
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
asked Nov 21 '18 at 20:06
pingOfDoompingOfDoom
581113
581113
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03
add a comment |
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03
add a comment |
2 Answers
2
active
oldest
votes
For reference, I never really figured out why this was going on, but I fixed it by only including the delete cascade on one of the 2 foreign keys
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
add a comment |
Going by the trigger way to accomplish your task try to set values with an arbitrary value like 99999 and mark it as delete key in parent table , so the things can make sense
but the data will remain forever in child tables.
or
1. set reference keys to null
2. Disable constraint
3. Delete data in child tables having ref key as null
4. Enable constraint
5. Delete parent keys
answered Dec 11 '18 at 2:33
YuganshYugansh
354
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53419734%2fsql-server-on-delete-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
For reference, I never really figured out why this was going on, but I fixed it by only including the delete cascade on one of the 2 foreign keys
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
add a comment |
For reference, I never really figured out why this was going on, but I fixed it by only including the delete cascade on one of the 2 foreign keys
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
add a comment |
For reference, I never really figured out why this was going on, but I fixed it by only including the delete cascade on one of the 2 foreign keys
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
For reference, I never really figured out why this was going on, but I fixed it by only including the delete cascade on one of the 2 foreign keys
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
answered Dec 11 '18 at 1:23
pingOfDoompingOfDoom
581113
581113
add a comment |
add a comment |
Going by the trigger way to accomplish your task try to set values with an arbitrary value like 99999 and mark it as delete key in parent table , so the things can make sense
but the data will remain forever in child tables.
or
1. set reference keys to null
2. Disable constraint
3. Delete data in child tables having ref key as null
4. Enable constraint
5. Delete parent keys
answered Dec 11 '18 at 2:33
YuganshYugansh
354
add a comment |
Going by the trigger way to accomplish your task try to set values with an arbitrary value like 99999 and mark it as delete key in parent table , so the things can make sense
but the data will remain forever in child tables.
or
1. set reference keys to null
2. Disable constraint
3. Delete data in child tables having ref key as null
4. Enable constraint
5. Delete parent keys
answered Dec 11 '18 at 2:33
YuganshYugansh
354
add a comment |
Going by the trigger way to accomplish your task try to set values with an arbitrary value like 99999 and mark it as delete key in parent table , so the things can make sense
but the data will remain forever in child tables.
or
1. set reference keys to null
2. Disable constraint
3. Delete data in child tables having ref key as null
4. Enable constraint
5. Delete parent keys
answered Dec 11 '18 at 2:33
YuganshYugansh
354
Going by the trigger way to accomplish your task try to set values with an arbitrary value like 99999 and mark it as delete key in parent table , so the things can make sense
but the data will remain forever in child tables.
or
1. set reference keys to null
2. Disable constraint
3. Delete data in child tables having ref key as null
4. Enable constraint
5. Delete parent keys
answered Dec 11 '18 at 2:33
YuganshYugansh
354
answered Dec 11 '18 at 2:33
YuganshYugansh
354
answered Dec 11 '18 at 2:33
YuganshYugansh
354
answered Dec 11 '18 at 2:33
YuganshYugansh
354
354
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53419734%2fsql-server-on-delete-event%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is the exact wording of the deployment error you're getting?
– Robert Harvey♦
Nov 21 '18 at 20:08
Its about how the foreign key constraint should be changed because of the possibility of cycles or multiple cascade paths(I'll make that clearer in the question definition sorry)
– pingOfDoom
Nov 21 '18 at 20:09
Could it be the case that if I have an "On Delete SET" condition on the foreign keys in bar, and an onDelete trigger on foo to delete entries in bar, that the setting and deleting operations may not be happening how i think they are and that's what's causing the "cycles and cascade paths" error?
– pingOfDoom
Nov 21 '18 at 20:30
My guess is that SQL can't know if there's ever going to be the same fooid in both fooRef1 and fooRef2; if that's the case, it would have to delete the same record two times. TBH, having two fields pointing to the same field in the same table seems like an odd design choice, but I guess that's out of the scope
– Josh Part
Dec 11 '18 at 2:03