多对多自我参照表 [英] Many-to-many self-referential table

问题描述

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

   - 单词列表
 CREATE TABLE word（
 id integer PRIMARY KEY，
 word varchar（32） NOT NULL UNIQUE 
）; 
 INSERT INTO字（id，word）VALUES（1，'revolve'）; 
 INSERT INTO字（id，word）VALUES（2，'rotate'）; 
 
  -  M：M之间的链接
 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关系？

解决方案

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

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?

解决方案

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.

这篇关于多对多自我参照表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！