DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.no;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=CnTt3SKGAtuLzXHg1Aa4JcU+3s9Dd1+KuAYATTxiC4sVd3UKpzOY099j9Fo/lCXfce99wC4+KVPizl13yNpWoZI37vDyxia8ArLMmJPkdA9PBDBsPARifAJ6QOey6WHTHooIbQONDW/Vif1E9jZtxSwPPaqQxr+87kfD9HTH4Io=;
Message-ID: <334403.71161.qm@web27105.mail.ukl.yahoo.com>
Date: Wed, 29 Jul 2009 20:16:54 +0000 (GMT)
From: Kjell Rune Skaaraas <kjella79@yahoo.no>
Subject: Foreign keys and fixed values...
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable


Hello,

I'm having a problem using foreign keys, or at least in a way i find good. =
Basicly I have a table I wish to use as a foreign key with lookup "id" and =
"code", like:

1,a
1,b
1,c
2,a
2,d

I have of course an UNIQUE on (id,code). However, in the table I want the k=
ey to apply to, I only have one column that I want to restrict to one looku=
p. Basicly I want to do something like:

ALTER TABLE data ADD FOREIGN KEY ( 2, col ) REFERENCES lookups ( id, code )

In this case 2 is a literal integer, not a column reference and the result =
should be that a and d is legal - but this command isn't legal.

Options:
1) Add another column to my data table like:
ALTER TABLE data ADD COLUMN dummy INTEGER DEFAULT 2
ALTER TABLE data ADD FOREIGN KEY ( dummy, col ) REFERENCES lookups ( id, co=
de )
This works, but creates a useless column in my data table that feels very u=
nclean.

2) Refactor the lookup table to have one lookup per table. However, this al=
so means I'll probably need to duplicate translation tables, value mapping =
tables etc. and a bunch of queries would have to dynamically alter table na=
mes. It seems more than a little messy.

3) Forego integrity checking in the database and do it in the app. However,=
 I sense trouble with that apporach in the future as I hope several app box=
es can do work on the same database.

4) Trigger? Something else?

The way I'd like to do it seems like the cleanest, if only it'd work. Why c=
an't the foreign key evaluate the literal, in the same way it could in a qu=
ery? It's possible that I'm way outside all SQL standards now but it seems =
like sensible functionality to me.

Regards,
Kjell Rune=0A=0A=0A      __________________________________________________=
_______=0AAlt i ett. F=E5 Yahoo! Mail med adressekartotek, kalender og=0Ano=
tisblokk. http://no.mail.yahoo.com