title: Postgresql foreign key -- no unique constraint
body: <p>I have the following case:</p>
  
  <p>I have a table that contains text entries in various languages. It is defined as follows:</p>
  
  <pre><code>    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
      );
  </code></pre>
  
  <p>the languages table is just a list of language codes and it is not that relevant.</p>
  
  <p>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.</p>
  
  <p>At first I created this:</p>
  
  <pre><code>    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
      );
  </code></pre>
  
  <p>which fails with </p>
  
  <blockquote>
    <p>ERROR:  there is no unique constraint matching given keys for referenced table "text"</p>
  </blockquote>
  
  <p>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 ... </p>
  
  <p>Is there a more elegant way to go around this problem ? </p>