Thread: Making Complicated References
Hi all, I have tables A, B, and C. foo within Table B & C references foo in table A. I want bar in table C to reference bar in table B -- but I want it to reference only those bars in B with a foo that is the same. Is that possible? Dan
On Sun, Mar 30, 2003 at 03:06:46 +0000, "Daniel R. Anderson" <dan@mathjunkies.com> wrote: > Hi all, > > I have tables A, B, and C. foo within Table B & C references foo in > table A. I want bar in table C to reference bar in table B -- but I > want it to reference only those bars in B with a foo that is the same. > > Is that possible? If foo + bar is a candidate key for B, then you can use a multicolumn foriegn key. If foo + bar isn't a candidate key for B, then I think you will need to explain more about what you are trying to do.
How about some table definitions? (amazing! what everyone else asked me for before they'd help me when **I** first got here, now I ask others to supply :-) Bruno Wolff III wrote: > On Sun, Mar 30, 2003 at 03:06:46 +0000, > "Daniel R. Anderson" <dan@mathjunkies.com> wrote: > >>Hi all, >> >>I have tables A, B, and C. foo within Table B & C references foo in >>table A. I want bar in table C to reference bar in table B -- but I >>want it to reference only those bars in B with a foo that is the same. >> >>Is that possible? > > > If foo + bar is a candidate key for B, then you can use a multicolumn > foriegn key. If foo + bar isn't a candidate key for B, then I think you > will need to explain more about what you are trying to do. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
<snip>
> How about some table definitions?
</snip>
below is a simplified version:
CREATE TABLE products
(
product_key varchar(80), primary key,
attribute_1 varchar(80), not null,
attribute_2 varchar(80), not null,
...
attribute_n varchar(80) not null
);
CREATE TABLE sizes
(
product_key varchar(80), references products,
packaging_type varchar(80), -- i.e. Bags
-- i.e. Drums
-- i.e. Bottles
weight varchar(80) -- i.e. 5lbs
-- i.e. 1 gallon
-- i.e. 8 oz.
);
So now I want to create a table called orders. This table must
reference the item_number in products AND reference the packaging type
and weight so that if somebody orders stearic acid they can't select a
4.3 lb bag if we only sell 50lb bags.
This is what I mean by ¨complicated references¨
Thanks in advance,
-Dan
>>>>> "DanielD" == Daniel R Anderson <dan@mathjunkies.com> writes: DanielD> <snip> >> How about some table definitions? DanielD> </snip> DanielD> below is a simplified version: DanielD> CREATE TABLE products DanielD> ( DanielD> product_key varchar(80), primary key, DanielD> attribute_1 varchar(80), not null, DanielD> attribute_2 varchar(80), not null, DanielD> ... DanielD> attribute_n varchar(80) not null DanielD> ); DanielD> CREATE TABLE sizes DanielD> ( DanielD> product_key varchar(80), references products, DanielD> packaging_type varchar(80), -- i.e. Bags DanielD> -- i.e. Drums DanielD> -- i.e. Bottles DanielD> weight varchar(80) -- i.e. 5lbs DanielD> -- i.e. 1 gallon DanielD> -- i.e. 8 oz. DanielD> ); DanielD> So now I want to create a table called orders. This table must DanielD> reference the item_number in products AND reference the packaging type DanielD> and weight so that if somebody orders stearic acid they can't select a DanielD> 4.3 lb bag if we only sell 50lb bags. That's not normalized. Your type/weight depends partially on your key. What you really have is a product with a size and weight, and a foreign key to an attribute set for a collection of products. Then your order refers to a particular product, and you can drill down from there to get to the common attributes. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!