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 key
to apply to, I only have one column that I want to restrict to one lookup.
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, code )
This works, but creates a useless column in my data table that feels very
unclean.
2) Refactor the lookup table to have one lookup per table. However, this also
means I'll probably need to duplicate translation tables, value mapping tables
etc. and a bunch of queries would have to dynamically alter table names. 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 boxes 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 can't
the foreign key evaluate the literal, in the same way it could in a query? It's
possible that I'm way outside all SQL standards now but it seems like sensible
functionality to me.
Regards,
Kjell Rune
_________________________________________________________
Alt i ett. Få Yahoo! Mail med adressekartotek, kalender og
notisblokk. http://no.mail.yahoo.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
