Postgresql 外键——没有唯一约束

Postgresql foreign key -- no unique constraint

提问by user3017303

I have the following case:

我有以下情况：

I have a table that contains text entries in various languages. It is defined as follows:

我有一个表格，其中包含各种语言的文本条目。它的定义如下：

    CREATE TABLE text
    (
      textid character varying(70) NOT NULL,
      language character varying(10) NOT NULL,
      content text NOT NULL,
      CONSTRAINT text_pk PRIMARY KEY (textid , language ),
      CONSTRAINT languages_text_fk FOREIGN KEY (language)
          REFERENCES languages (language) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE RESTRICT
    );

the languages table is just a list of language codes and it is not that relevant.

语言表只是一个语言代码列表，并没有那么重要。

Now, on another table I need to reference a piece of text, but I don't need, and I don't know the language with which the text will be retrieved. I only know the id of the text to retrieve. The actual language will be dictated at run time by the user reading the text.

现在，在另一个表上，我需要引用一段文本，但我不需要，而且我不知道检索文本的语言。我只知道要检索的文本的 ID。实际语言将在运行时由阅读文本的用户指定。

At first I created this:

起初我创建了这个：

    CREATE TABLE content_text
    (
      contentid character varying(70) NOT NULL,
      textid character varying(70) NOT NULL,
      CONSTRAINT content_text_pk PRIMARY KEY (contentid , textid ),
      CONSTRAINT content_text_text_fk FOREIGN KEY (textid)
          REFERENCES text (textid) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE RESTRICT
    );

which fails with

失败了

ERROR: there is no unique constraint matching given keys for referenced table "text"

错误：没有唯一约束匹配给定键的引用表“文本”

I know that in order to avoid this error I have to create an intermediate table containing only textid which will be referenced both by content and by text, but I really feel that this is an overkill ... a table just for this, which in my mind although textid is NOT unique, it does not make sense to be rejected ...

我知道为了避免这个错误，我必须创建一个只包含 textid 的中间表，它将被内容和文本引用，但我真的觉得这是一个矫枉过正的......我的想法是虽然 textid 不是唯一的，但被拒绝是没有意义的......

Is there a more elegant way to go around this problem ?

有没有更优雅的方法来解决这个问题？

回答by Denis de Bernardy

I know that in order to avoid this error I have to create an intermediate table containing only textid which will be referenced both by content and by text, but I really feel that this is an overkill … Is there a more elegant way to go around this problem ?

我知道为了避免这个错误，我必须创建一个只包含 textid 的中间表，它将被内容和文本引用，但我真的觉得这是一种矫枉过正……有没有更优雅的方法来解决这个问题问题 ？

No, there isn't.

不，没有。

It's also part of the SQL spec insofar as I'm aware: foreign keys must reference a unique column.

据我所知，它也是 SQL 规范的一部分：外键必须引用唯一的列。

Also, are you absolutely sure that this intermediary table isn't going to turn out to be useful at some point? Say, to contain meta data such as a parent_text_id in case you ever introduce some hierarchy? Or more to the point: "to reference a piece of text" without knowing or needing "the language with which the text will be retrieved."

另外，你绝对确定这个中间表在某个时候不会有用吗？比如说，要包含诸如 parent_text_id 之类的元数据，以防您引入一些层次结构？或者更重要的是：在不知道或不需要“检索文本的语言”的情况下“引用一段文本”。

回答by Richard

Since you have created the table TEXTwith the primary key ( textid, language ) your foreign key in CONTEXT_TEXTmust refer to the same primary key. Thus:

由于您TEXT使用主键（ textid, language ）创建了表，因此您的外键CONTEXT_TEXT必须引用相同的主键。因此：

 CREATE TABLE content_text
    (
      contentid character varying(70) NOT NULL,
      language character varying(10) NOT NULL,
      textid character varying(70) NOT NULL,
      CONSTRAINT content_text_pk PRIMARY KEY (contentid , textid),
      CONSTRAINT content_text_text_fk FOREIGN KEY (textid, language)
          REFERENCES text (textid, language) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE RESTRICT
    );

Alternatively you could remove languagefrom the primary key on TEXT. Instead you could create a second unique index :

或者，您可以language从 上的主键中删除TEXT。相反，您可以创建第二个唯一索引：

  CREATE TABLE text2
    (
      textid character varying(70) NOT NULL,
      language character varying(10) NOT NULL,
      content text NOT NULL,
      CONSTRAINT text_pk PRIMARY KEY (textid),
      CONSTRAINT languages_text_fk FOREIGN KEY (language)
          REFERENCES languages (language) MATCH SIMPLE
          ON UPDATE CASCADE ON DELETE RESTRICT, 
      unique (textid, language)
    );