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@norcov.com I can't keep up with the list right now.
>>
>>thanks
>>~e
>
>:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
>     elein@norcov.com   (510)543-6079
>     "Taking a Trip. Not taking a Trip." --anonymous
>:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:
>

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