Re: Foreign key question

On Thu, 15 Jan 2004, [ISO-8859-1] Victor Spång Arthursson wrote:

> New to foreign keys and have some questions
>
> The first is, i have a language table with a primary key on the fields
> lang and relid:
>
> relid | lang | text
> -------+------+-------------
> 11111 | uk | hello
> 11111 | dk | hej
> 11111 | de | guten tag
> 11112 | uk | world
> 11112 | dk | værld
>
> In another table, texts, I have the following:
>
> id | text
> -------+------+
> 4 | 11112 |
>
> What I want to do is to create a foreign key between the two tables.
> But trying to do so, with the following syntax, I get the following
> error message:
>
> > ALTER TABLE varer ADD CONSTRAINT varenavn FOREIGN KEY (varenavn)
> REFERENCES languages(relid) MATCH FULL ON DELETE CASCADE;
>
> >ERROR: there is no unique constraint matching given keys for
> referenced table "languages"
>
> Can't i create a Foreign key to a field that is not defined as UNIQUE?

You are not allowed to do so by the SQL spec, no.

> And if so, are there any other approach to solve this problem?

I think you could keep the ids for text blocks in a separate table with
all tables that have such an id keeping a reference to it (thus there's a
separate list of valid ids). Unfortunately depending on the behavior you
want, you may have to write triggers to keep the values straight (for
example if you want the id to go away if all the references in a
particular table go away).