Cascading Delete Without Foreign Key Constraint Postgresql
-1
I want to setup a cascading delete between two tables without enforcing referential integrity.
For example, imagine I've got the following two tables:
posts
+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+---------+
users
+----+
| id |
+----+
| 1 |
| 4 |
+----+
I want to create a constraint/trigger that causes the referenced row in users to be deleted when I delete a row in posts without enforcing referential integrity.
sql postgresql foreign-keys cascading-deletes
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
|
show 5 more comments
-1
I want to setup a cascading delete between two tables without enforcing referential integrity.
For example, imagine I've got the following two tables:
posts
+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+---------+
users
+----+
| id |
+----+
| 1 |
| 4 |
+----+
I want to create a constraint/trigger that causes the referenced row in users to be deleted when I delete a row in posts without enforcing referential integrity.
sql postgresql foreign-keys cascading-deletes
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
1
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
1
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
2
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18
|
show 5 more comments
-1
-1
-1
I want to setup a cascading delete between two tables without enforcing referential integrity.
For example, imagine I've got the following two tables:
posts
+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+---------+
users
+----+
| id |
+----+
| 1 |
| 4 |
+----+
I want to create a constraint/trigger that causes the referenced row in users to be deleted when I delete a row in posts without enforcing referential integrity.
sql postgresql foreign-keys cascading-deletes
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
I want to setup a cascading delete between two tables without enforcing referential integrity.
For example, imagine I've got the following two tables:
posts
+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+---------+
users
+----+
| id |
+----+
| 1 |
| 4 |
+----+
I want to create a constraint/trigger that causes the referenced row in users to be deleted when I delete a row in posts without enforcing referential integrity.
sql postgresql foreign-keys cascading-deletes
sql postgresql foreign-keys cascading-deletes
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
edited Nov 23 '18 at 13:41
James Stonehill
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
asked Nov 23 '18 at 11:43
James StonehillJames Stonehill
404414
404414
1
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
1
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
2
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18
|
show 5 more comments
1
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
1
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
2
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18
1
1
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
1
1
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
2
2
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in theuserstable.
– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
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
});
}
});
draft saved
draft discarded
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%2f53446075%2fcascading-delete-without-foreign-key-constraint-postgresql%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
0
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in theuserstable.
– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
add a comment |
0
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in theuserstable.
– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
add a comment |
0
0
0
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
edited Nov 23 '18 at 14:08
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
answered Nov 23 '18 at 13:48
a_horse_with_no_namea_horse_with_no_name
298k46452548
298k46452548
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in theuserstable.
– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
add a comment |
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in theuserstable.
– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in the
users table.– Damien_The_Unbeliever
Nov 23 '18 at 13:51
Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in the
users table.– Damien_The_Unbeliever
Nov 23 '18 at 13:51
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
@Damien_The_Unbeliever: ah, you are right.
– a_horse_with_no_name
Nov 23 '18 at 14:03
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53446075%2fcascading-delete-without-foreign-key-constraint-postgresql%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
1
Question would be why create something like that when cascade delete can do it for you
– George Joseph
Nov 23 '18 at 11:47
The db already has data in it and the data does not respect referential integrity.
– James Stonehill
Nov 23 '18 at 11:48
1
What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on Minimal, Complete, and Verifiable example. Please clarify via post edits, not comments.
– philipxy
Nov 23 '18 at 12:11
This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.
– philipxy
Nov 23 '18 at 12:12
2
I adjusted the sample table data to visualize "The db already has data in it and the data does not respect referential integrity".
– jarlh
Nov 23 '18 at 13:18