71 References to non-unique columns

>>So with 7.1 a REFERENCE constraint on a column to another
>>column must be unique.
>>
>>So now what about referentially integrity for one to many join
>>columns?
>>
>>I have table foo that does a one to many join on a reference table bar.
>>create table foo (
>> colone int,
>> colref varchar(3), -- REFERENCES bar(colone)
>> PRIMARY KEY (colone));
>>create table bar (
>> bcolone varchar(3),
>> bcoltwo text,
>> PRIMARY KEY( bcolone, bcoltwo));
>>
>>I would like to have foo.colref validated as at least one entry in
>>bar.bcolone
>>But the new requirement that REFERENCES must be UNIQUE
>>screws me up. I don't want to add bar's second key column, bcoltwo,
>>to table foo and then create a foreign key, because it does not describe
>>what I really mean which is that foo references a set of rows in bar.
>>
>>Work arounds:
>>works:
>> Check ( f(colref) ) where f() does the subselect seems to
>> work. Is this what
>> we *should* do? It seems a little wordy.
>>
>> Put in the second key and ignore it. Aesthetically bleak.
>>
>>probably works
>> Trigger -- more or less like check ( f(colref) ).
>>
>>doesn't work:
>>
>> Check ( colref in (select bcolone from bar)) tells me I can't
>> use a subselect
>> in a check clause.
>>
>>Please confirm that there is a dilemma with icky work arounds
>>or point me back to the Right Answer (or both :-)
>>
>>Reply to elein(at)norcov(dot)com I can't keep up with the list right now.
>>
>>thanks
>>~e
>
>:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
> elein(at)norcov(dot)com (510)543-6079
> "Taking a Trip. Not taking a Trip." --anonymous
>:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
>

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
elein(at)norcov(dot)com (510)543-6079
"Taking a Trip. Not taking a Trip." --anonymous
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~: