多对多自引用表
有没有好的方法来实现单个表中行之间的多对多关系？
示例：存储单词同义词的表：
-- 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)
);
明显的解决方案可能会导致非 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 关系呢？
Is there a good way to implement many-to-many relation between rows in single table?
Example: table to store word synonyms:
-- 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)
);
Obvious solution results in probably not-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 dealt by adding (word1 < word2) check, it makes SELECTs much more complex (union comparing to trivial join) and is pretty arbitrary. This specific case can benefit from auxiliary table (such as 'meaning', so words are M:N linked to common meaning and not to each other, giving cleaner schema), but I'm interested in some general solution.
So is there a better (and hopefully common) way to implement such M:M relation?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在这种情况下，我会在 UPDATE 和 INSERT 上添加检查约束，以强制 word1 始终小于 word2，反之亦然。
In this case I'd add a CHECK CONSTRAINT on UPDATE and on INSERT to enforce that word1 is always less than word2 and vice-versa.
我将创建一个如下视图：
这样，您始终拥有一个干净、无重复的列表，可以轻松从中进行选择。 我发现这与建立多对多关系一样干净。
I'd create a view that was the following:
That way, you always have a clean, no duplicate list that's easy to select from. I've found that's about as clean of a way as you can have to do a many-to-many relationship.