Postgresql foreign key — no unique constraint

问题:

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 ?

回答1:

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

回答2:

Since you have created the table TEXT with the primary key ( textid, language ) your foreign key in CONTEXT_TEXT must refer to the same primary key. Thus:

 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 language from the primary key on TEXT. Instead you could create a second unique index :

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