Postgresql外键 - 没有唯一的约束 [英] Postgresql foreign key -- no unique constraint

问题描述

我有以下情况：

我有一个包含各种语言的文本项的表。它定义如下：

pre code $ CREATE TABLE

textid字符变化（70）NOT NULL ，
语言字符变化（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
）;

语言表只是一个语言代码列表，并不是相关的。

现在，在另一张桌子上，我需要引用一段文字，但是我不需要，而且我不知道文本将被检索的语言。我只知道要检索的文本的ID。

起初我创建了这个：

（$ 70）NOT NULL，
textid字符变化（70）NOT NULL，
CONSTRAINT（
$> $ code> CREATE TABLE content_text
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
）;

不符合

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

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

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

解决方案

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

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

另外，你是否确定这个中间表在某些时候不会变得有用？比如说，为了包含元数据，比如parent_text_id，以便引入一些层次结构？或者更重要的是：引用一段文本而不​​知道或者不需要文本将被检索的语言。

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.

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 ...

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

解决方案

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 ?

No, there isn't.

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

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."

这篇关于Postgresql外键 - 没有唯一的约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！