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: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

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), ...

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.

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 ?


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-2017 The PostgreSQL Global Development Group