Thread: foreign key help

I am building a database that has about 20 fields in various tables that
  I would like to constrain to a small group (<=5) of choices.  Rather
that refer to 20 seperate tables, I would like to refer to 1 table that
contained category and choice fields.

for example

choices table

category | choice
-----------------
color    | red
color    | green
size     | small
size     | big
shape    | circle
shape    | square

item table

item     |  shape
-----------------
box      |  constrain to choices(choice) where category='shape'



Is there an easy way to do this, or is it better to have many
choices_category tables?

Thanks,

Dale

You may want to have a look at pg DOMAIN (also conforms to SQL standard).

http://www.postgresql.org/docs/8.0/static/sql-createdomain.html

Regards,
Qingqing


"Dale Sykora" <dalen@czexan.net> writes
> I am building a database that has about 20 fields in various tables that
>   I would like to constrain to a small group (<=5) of choices.  Rather
> that refer to 20 seperate tables, I would like to refer to 1 table that
> contained category and choice fields.
>
> for example
>
> choices table
>
> category | choice
> -----------------
> color    | red
> color    | green
> size     | small
> size     | big
> shape    | circle
> shape    | square
>
> item table
>
> item     |  shape
> -----------------
> box      |  constrain to choices(choice) where category='shape'
>
>
>
> Is there an easy way to do this, or is it better to have many
> choices_category tables?
>
> Thanks,
>
> Dale
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>