Model many-to-many relationships

A post can have many tags, and a tag can belong to many posts. Neither table has one column that can represent the complete relationship.

Add a junction table:

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Each row represents one relationship. The composite primary key prevents the same tag from being attached to the same post twice.

Add a tag to a post with an ordinary insert:

INSERT INTO post_tags (post_id, tag_id)
VALUES (12, 3);

Read all tags for the post:

SELECT tags.name
FROM tags
JOIN post_tags ON post_tags.tag_id = tags.id
WHERE post_tags.post_id = 12
ORDER BY tags.name;

The junction can hold facts about the relationship itself. If tags are attached by users, add columns such as added_by and added_at to post_tags, not to posts or tags.

Without the primary key or a unique constraint, duplicate junction rows can multiply join results and counts. DISTINCT might hide the symptom, but enforcing the real rule prevents the bad state.

The cascading deletes above are deliberate: a relationship row has no meaning after its post or tag disappears. Review that choice for your own data.

Your action is to connect two posts to two tags, attempt a duplicate relationship, and query both directions: tags for one post and posts for one tag.