| 1 | -- Which issues carry which labels. |
| 2 | -- |
| 3 | -- A join table with no fields of its own and no id: the pair *is* the row, so the |
| 4 | -- primary key is the pair and there is nothing else to say about it. `unique` falls out |
| 5 | -- of that for free, which is what makes assigning the same label twice a no-op rather |
| 6 | -- than a duplicate. |
| 7 | -- |
| 8 | -- Both foreign keys cascade, and they mean different things. Deleting an issue takes |
| 9 | -- its labels off it, like its comments. Deleting a **label** takes it off every issue |
| 10 | -- that wore it — the issues stay, they simply stop wearing it, because a label is a way |
| 11 | -- of describing an issue rather than part of what the issue says. |
| 12 | ( |
| 13 | issue_id text not null references issues (id) on delete cascade, |
| 14 | label_id text not null references labels (id) on delete cascade, |
| 15 | primary key (issue_id, label_id) |
| 16 | ); |
| 17 | |
| 18 | -- The primary key indexes `(issue_id, label_id)`, which covers reading one issue's |
| 19 | -- labels. This one covers the other direction: filtering the issues list to a label, |
| 20 | -- and counting how many issues each label is on. |
| 21 | (label_id, issue_id); |