Postgres: уникальная ссылка от А до Б

Question

Я хочу биекцию между парой (tag1, tag2) и tag_id.

CREATE TABLE tags (
         question_id INTEGER NOT NULL,
         tag_id SERIAL NOT NULL,
         tag1 VARCHAR(20),
         tag2 VARCHAR(20),
         PRIMARY KEY(question_id, tag_id),
         (tag1, tag2) UNIQUE references tags(tag_id)          #How?
     );

Я не хочу никаких ссылок, таких как:

(PHP, Perl) points to 1 and 2,
3 points to (C#, null) and (Python, Elinks)

Другими словами, я хочу, чтобы ССЫЛКА была уникальной ОТ (tag1, tag2) TO тегов (tag_id), а не UNIQUE(tag1, tag2).

Answer 1

Это может быть больше похоже на то, что вы ищете:

CREATE TABLE tags (
    question_id INTEGER NOT NULL,
    tag_id SERIAL NOT NULL,
    tag1 VARCHAR(20),
    tag2 VARCHAR(20),
    PRIMARY KEY (tag_id),
    INDEX (question_id),
    UNIQUE (tag1, tag2)
);

Если сделать tag_id первичным ключом, это означает, что вы можете иметь только одну запись с заданным tag_id, и что поиск на основе tag_id будет быстрым.

Индекс 'question_id' улучшит скорость поиска на основе 'question_id', что, как я думаю, вы пытались сделать с вашим первоначальным определением PRIMARY KEY. Если вы действительно хотите, чтобы пара (tag_id, question_id) была уникальной, как у вас, добавьте туда UNIQUE (tag_id, question_id), но я бы сказал, что вы должны оставить tag_id в качестве первичного ключа.

Ограничение уникальности (tag1, tag2) предотвращает дублирование обратного отображения.

Вот несколько примеров того, что может работать:

Работает:

1 -> (х, у)

2 -> (x, z)

Сбой (tag_id является первичным ключом и, следовательно, уникальным):

1 -> (х, у)

1 -> (у, х)

Ошибка (пара (tag1, tag2) не уникальна):

1 -> (х, у)

2 -> (х, у)

Однако пара (x, y) не равна паре (y, x). Я не уверен, как поймать это ограничение уникальности.