Many to Many: A Table in the Middle

Some links go both ways. A product can have many tags (gift, eco, bestseller), and one tag sits on many products. That is a many-to-many link. No single column can hold it, because a column keeps one value per row.

The table in the middle: a third table holds one row per link. Each row has one id from each side, and each id REFERENCES its own table. The primary key is the pair of ids, so the same link can't be stored twice. Students and courses work the same way:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id INTEGER NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
)

To read names, walk through the middle with two joins: first from the first table to its links, then from each link to the second table. A row with no links finds no match, so a plain JOIN leaves it out.

The shop already has a tags table: 1 gift, 2 eco, 3 bestseller. The check runs query and adds the links (1, 1), (1, 3), (2, 2), (4, 3), (5, 1), (5, 2), (6, 1) and (8, 1). Then it tries to add (1, 1) a second time and shows tag_query. It must print:

INSERT INTO product_tags VALUES (1, 1)
  Error: UNIQUE constraint failed: product_tags.product_id, product_tags.tag_id
product | tag
Mug | gift
Mug | bestseller
Notebook | eco
Pen | bestseller
Plant | gift
Plant | eco
Teapot | gift
Gift card | gift
(8 rows)

Your task: in query, create product_tags with the columns product_id and tag_id. Each one points at its own table, and the pair is the primary key. In tag_query, list each product's name next to the name of each of its tags.

Assignment
Follow the Coding Tutorial to create the product_tags table and join the products to their tags through it.

Hint
The primary key goes on its own line after the two columns, with no comma after it. Both products and tags have a name column. Put the table's name in front of each one and give it an AS name.