sql 2020-09-29 | Slack Archive

emccue21:09:45

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
)

dharrigan07:09:48

Here's a simple example using a cascade delete, with also a trigger

dharrigan07:09:49

https://gist.github.com/dharrigan/7d5c6474cd56f0a17922f106c9968036

dharrigan07:09:41

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.

dharrigan07:09:57

you probably either want to put a restrict in, or remove the cascade delete on the tag 🙂

dharrigan07:09:23

(and let the trigger do the delete for you)

emccue21:09:52

say this is my schema definition

emccue21:09:56

(postgres)

emccue21:09:22

if a post is deleted, will just the (post_id, tag_id) be deleted

emccue21:09:34

or will the referenced tag be deleted as well