Skip site navigation (1) Skip section navigation (2)

Funny foreign keys

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Funny foreign keys
Date: 2009-04-29 14:57:49
Message-ID: 49F86AED.6040900@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

I'd like to use a lookup-table that contains text-values and their keys.

There is a data-table with entities that have (among others) 2 
attributes which could nicely be represented by numbers.
e.g.  enttity (e_id, ..., color_fk, shape_fk, ...)

Now I could create 2 tables with the text:
color (-1, 'not provided'), (0, NULL), (1, 'red'), (2, 'green'), (3, 
'blue'), ...
shape (-1, 'not provided'), (0, NULL), (1, 'point'), (2, 'line'), (3, 
'triangle'), (4, 'circle'), ...

There might come more such attributes.
Therfore I'd rather have just 1 attribute-table with a 3rd column that 
has a group-id:
attributes (attribute_id, attribute, attribute_group)
(-1, 'not provided', 0), (0, NULL, 0),
(100, 'red', 1), (110, 'green', 1), (120, 'blue', 1), ...
(200, 'point', 2), (210, 'line', 2), (220, 'triangle', 2), (230, 
'circle', 2), ...

Easy:
Within the entities-table I can define 2 fk-constraints on color_fk, 
shape_fk which reference the cumulative attribute-table.
The application should make sure that the user could only chose from 
group 0 (shared) and one other.

Difficult
I'd like to let the DB control this to not risk that shape-ids end up in 
the color_fk.
How can I make shure, that color_fk only receives values from 
attribute-groups 0 and 1 but no other?

Probaply I got it philosophically wrong ?

Responses

pgsql-novice by date

Next:From: Marcin KrolDate: 2009-04-29 15:19:48
Subject: Re: retrieving primary key for row with MIN function
Previous:From: Sean DavisDate: 2009-04-29 13:08:21
Subject: Re: retrieving primary key for row with MIN function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group