[SOLVED] Postgres: Unique reference from A to B

Postgres: Unique reference from A to B

I want a bijection between the pair (tag1, tag2) and 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?
     );

I want no reference such as:

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

In other words, I want the REFERENCE to be unique FROM (tag1, tag2) TO tags(tag_id), not UNIQUE(tag1, tag2).

Solution

This might be more like what you are looking for:

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)
);

Making 'tag_id' the primary key means that you can only have one entry with a given 'tag_id', and that searches based on 'tag_id' will be fast.

The index on 'question_id' will improve search speed based on 'question_id', which is what I think you were trying to do with your original PRIMARY KEY definition. If you really want the (tag_id, question_id) pair to be unique, as you had it, then add a UNIQUE (tag_id, question_id) in there, but I would say that you should leave tag_id as the primary key.

The uniqueness constraint on (tag1, tag2) prevents the reverse mapping from having duplicates.

Here are a few examples of what can work:

Works:

1 -> (x, y)

2 -> (x, z)

Fails (tag_id is a primary key, and therefore is unique):

1 -> (x, y)

1 -> (y, x)

Fails (the pair (tag1, tag2) is not unique):

1 -> (x, y)

2 -> (x, y)

However, the pair (x, y) is not equal to the pair (y, x). I'm not sure how to catch that uniqueness constraint.