Tables with many recommendations

Question

Is there a good way to implement a many-to-many relationship between rows in a separate table?

Example: a table for storing synonyms of words:

-- list of words
CREATE TABLE word (
    id    integer      PRIMARY KEY,
    word  varchar(32)  NOT NULL UNIQUE
);
INSERT INTO words (id, word) VALUES (1, 'revolve');
INSERT INTO words (id, word) VALUES (2, 'rotate');

-- M:M link between words
CREATE TABLE word_link (
    word1  integer      REFERENCES word(id) NOT NULL,
    word2  integer      REFERENCES word(id) NOT NULL,
    PRIMARY KEY (word1, word2)
);

The obvious solution is that there is probably a non-1NF table containing duplicate data:

INSERT INTO word_link(word1, word2) VALUES (1, 2);
INSERT INTO word_link(word1, word2) VALUES (2, 1);

While duplication can be done by adding (word1 <word2) check, this makes SELECT much more complicated (union compared to trivial union) and quite arbitrary. This particular case may benefit from an auxiliary table (for example, “meaning”, so the words M: N are related to a common meaning, and not to each other, giving a cleaner scheme), but I'm interested in some general solution.

So, is there a better (and, hopefully, general) way to implement this M: M relationship?

Answer 1

CHECK CONSTRAINT UPDATE INSERT, , 1 2 .

Answer 2

, :

select distinct
    case when word1 < word2 then word1 else word2 end as word1,
    case when word1 < word2 then word2 else word1 end as word2
from
    word_link

, , , . , , , "--".