DELETE request on row with nested foreign keys
I have a Table A that is referenced to by Table B, and Table C has references to Table B.
Such that:
- Table A (pk: id)
- Table B (pk: id, fk: A_id)
- Table C (fk: B_id)
Every foreign key has a constraint ON_DELETE:CASCADE, however if I attempted to delete Table A - it refuses to. This is happening because rows in Table B have foreign keys pointing to Table A, and Table C has rows pointing to Table B.
I'm confident that I can resolve this issue by deleting Table B first, and then deleting Table A. However, would someone explain why this DELETE isn't possible? Surely, it should be able to perform a function of going down a 'hierarchy' of foreign keys and deleting everything in order?
Thanks
sql postgresql
asked Nov 12 '18 at 21:20
somekidsomekid
318
add a comment |
I have a Table A that is referenced to by Table B, and Table C has references to Table B.
Such that:
- Table A (pk: id)
- Table B (pk: id, fk: A_id)
- Table C (fk: B_id)
Every foreign key has a constraint ON_DELETE:CASCADE, however if I attempted to delete Table A - it refuses to. This is happening because rows in Table B have foreign keys pointing to Table A, and Table C has rows pointing to Table B.
I'm confident that I can resolve this issue by deleting Table B first, and then deleting Table A. However, would someone explain why this DELETE isn't possible? Surely, it should be able to perform a function of going down a 'hierarchy' of foreign keys and deleting everything in order?
Thanks
sql postgresql
asked Nov 12 '18 at 21:20
somekidsomekid
318
add a comment |
I have a Table A that is referenced to by Table B, and Table C has references to Table B.
Such that:
- Table A (pk: id)
- Table B (pk: id, fk: A_id)
- Table C (fk: B_id)
Every foreign key has a constraint ON_DELETE:CASCADE, however if I attempted to delete Table A - it refuses to. This is happening because rows in Table B have foreign keys pointing to Table A, and Table C has rows pointing to Table B.
I'm confident that I can resolve this issue by deleting Table B first, and then deleting Table A. However, would someone explain why this DELETE isn't possible? Surely, it should be able to perform a function of going down a 'hierarchy' of foreign keys and deleting everything in order?
Thanks
sql postgresql
asked Nov 12 '18 at 21:20
somekidsomekid
318
I have a Table A that is referenced to by Table B, and Table C has references to Table B.
Such that:
- Table A (pk: id)
- Table B (pk: id, fk: A_id)
- Table C (fk: B_id)
Every foreign key has a constraint ON_DELETE:CASCADE, however if I attempted to delete Table A - it refuses to. This is happening because rows in Table B have foreign keys pointing to Table A, and Table C has rows pointing to Table B.
I'm confident that I can resolve this issue by deleting Table B first, and then deleting Table A. However, would someone explain why this DELETE isn't possible? Surely, it should be able to perform a function of going down a 'hierarchy' of foreign keys and deleting everything in order?
Thanks
sql postgresql
sql postgresql
asked Nov 12 '18 at 21:20
somekidsomekid
318
asked Nov 12 '18 at 21:20
somekidsomekid
318
asked Nov 12 '18 at 21:20
somekidsomekid
318
asked Nov 12 '18 at 21:20
somekidsomekid
318
asked Nov 12 '18 at 21:20
somekidsomekid
318
318
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
In ON DELETE CASCADE operation, if a parent row is deleted, it will first delete the child row and then the parent row so as to ensure that no references are left as foreign key
As for your question, since Table B is referring to Table A 's primary key and Table C is referring to Table B's primary key, the relationship for which on delete cascade should work is just a parent child and not beyond.
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
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%2f53270296%2fdelete-request-on-row-with-nested-foreign-keys%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In ON DELETE CASCADE operation, if a parent row is deleted, it will first delete the child row and then the parent row so as to ensure that no references are left as foreign key
As for your question, since Table B is referring to Table A 's primary key and Table C is referring to Table B's primary key, the relationship for which on delete cascade should work is just a parent child and not beyond.
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
add a comment |
In ON DELETE CASCADE operation, if a parent row is deleted, it will first delete the child row and then the parent row so as to ensure that no references are left as foreign key
As for your question, since Table B is referring to Table A 's primary key and Table C is referring to Table B's primary key, the relationship for which on delete cascade should work is just a parent child and not beyond.
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
add a comment |
In ON DELETE CASCADE operation, if a parent row is deleted, it will first delete the child row and then the parent row so as to ensure that no references are left as foreign key
As for your question, since Table B is referring to Table A 's primary key and Table C is referring to Table B's primary key, the relationship for which on delete cascade should work is just a parent child and not beyond.
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
In ON DELETE CASCADE operation, if a parent row is deleted, it will first delete the child row and then the parent row so as to ensure that no references are left as foreign key
As for your question, since Table B is referring to Table A 's primary key and Table C is referring to Table B's primary key, the relationship for which on delete cascade should work is just a parent child and not beyond.
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
answered Nov 12 '18 at 21:36
Namandeep_KaurNamandeep_Kaur
12517
12517
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%2f53270296%2fdelete-request-on-row-with-nested-foreign-keys%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
