pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign keys and fixed values...
2+ messages / 2 participants
[nested] [flat]
* Foreign keys and fixed values...
@ 2009-07-29 20:16 Kjell Rune Skaaraas <kjella79@yahoo.no>
0 siblings, 0 replies; 2+ messages in thread
From: Kjell Rune Skaaraas @ 2009-07-29 20:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Foreign keys and fixed values...
@ 2009-07-30 15:33 Daryl Richter <ngzax@comcast.net>
0 siblings, 0 replies; 2+ messages in thread
From: Daryl Richter @ 2009-07-30 15:33 UTC (permalink / raw)
To: pgsql-sql
Hi-
On Jul 29, 2009, at 4:16 PM, Kjell Rune Skaaraas wrote:
>
> 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.
>
#2 is the proper solution.
A foreign key is a binding constraint between all the rows in the 2
tables. You are essentially trying to create a FK that applies to
some rows and not to others. Or, alternately, a table which has
conditional FKs to other tables based upon the data it contains.
So, you have created a dependency between the data in the database and
your schema which is a bad idea. Imagine, for example, after you have
set this all up, the user comes to you and says, "Sorry, when I told
you 2 maps to either a or d, I should have said 3". Now, instead of
just issuing an update, you have to change your schema.
Your other concerns regarding this option can almost certainly be
addressed through proper schema design too.
--
Daryl
http://itsallsemantics.com
"Everyone thinks of changing the world, but no one thinks of changing
himself."
- Leo Tolstoy
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-07-30 15:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-29 20:16 Foreign keys and fixed values... Kjell Rune Skaaraas <kjella79@yahoo.no>
2009-07-30 15:33 Re: Foreign keys and fixed values... Daryl Richter <ngzax@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox