database-design - 多对多自引用表

Question

有没有一种好方法可以实现单个表中行之间的多对多关系？

示例:存储单词同义词的表:

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

明显的解决方案可能导致 not-1NF 表，包含重复数据:

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

虽然可以通过添加 (word1 < word2) 检查来处理重复，但它使 SELECT 变得更加复杂(联合与普通连接相比)并且非常随意。这种特殊情况可以从辅助表中受益(例如“含义”，因此单词是 M:N 链接到共同含义而不是彼此链接，从而提供更清晰的架构)，但我对一些通用解决方案感兴趣。

那么有没有更好的(希望是通用的)方法来实现这种 M:M 关系？

Answer 1

在这种情况下，我会在 UPDATE 和 INSERT 上添加 CHECK CONSTRAINT 以强制 word1 始终小于 word2，反之亦然。