Clojurians Log v2
Clojure programming
Channels
Apps
sql
All things SQL and JDBC...
emccue 2020-09-29T21:19:45.011200Z
CREATE TABLE post_tag (
post_id integer not null,
tag_id integer not null,
CONSTRAINT fk_post
FOREIGN KEY (post_id)
REFERENCES post(id)
ON DELETE CASCADE,
CONSTRAINT fk_tag
FOREIGN KEY (post_id)
REFERENCES tag(id)
ON DELETE CASCADE
)
dharrigan 2020-09-30T07:04:48.015600Z
Here's a simple example using a cascade delete, with also a trigger
dharrigan 2020-09-30T07:04:49.015800Z
https://gist.github.com/dharrigan/7d5c6474cd56f0a17922f106c9968036
dharrigan 2020-09-30T07:05:41.016Z
btw, it's a bit dangerous, in the sense that if you delete a post, that references a tag, that then references another post, then that post will be removed too.
dharrigan 2020-09-30T07:05:57.016200Z
you probably either want to put a restrict in, or remove the cascade delete on the tag 🙂
dharrigan 2020-09-30T07:06:23.016400Z
(and let the trigger do the delete for you)
emccue 2020-09-29T21:19:52.011500Z
say this is my schema definition
emccue 2020-09-29T21:19:56.011700Z
(postgres)
emccue 2020-09-29T21:20:22.012300Z
if a post is deleted, will just the (post_id, tag_id) be deleted
emccue 2020-09-29T21:20:34.012600Z
or will the referenced tag be deleted as well
emccue 2020-09-29T21:22:51.013Z
and if its just the join table's tuple
emccue 2020-09-29T21:23:11.013500Z
how could I make it truly "crawl" the foreign keys and delete everything?
2020-09-29T22:59:59.013700Z
You could write a trigger to do that