Funny foreign keys
От | Andreas |
---|---|
Тема | Funny foreign keys |
Дата | |
Msg-id | 49F86AED.6040900@gmx.net обсуждение исходный текст |
Ответы |
Re: Funny foreign keys
|
Список | 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 ?
В списке pgsql-novice по дате отправления: