Postgresql foreign key -- no unique constraint
foreign-keys, postgresql, sql
Solution
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."
Problem
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 ?