Re: Can ORACLE do this?

Can't you have a foreign key constraint (manufacturer, color) on your detail table referencing the 'master colors table'?

Or have I missed something?

Rudy

 On Tue, 31 Aug 1999, Todder wrote:
>Thanks for the reply !!!
>
>I was hoping that Oracle could do this as we require this type of check
>a great deal.
>
>What confused me was that our database (Interbase) allowed the Check
>Constraint to compile but didn't work. Maybe I can talk them into
>allowing this for the next release.
>
>Todd
>
>In article <7qh4oq$voq$1_at_nnrp1.deja.com>,
> kal121_at_my-deja.com wrote:
>> No, Oracle cannot do this. A check constraint may never reference
>> another table. Maybe in future releases!
>>
>> In article <7qgvd7$rej$1_at_nnrp1.deja.com>,
>> Brian Yan <by999_at_hotmail.com> wrote:
>> > If I understand your question correctly, you can use trigger to
>> complete
>> > this (for example, before insert trigger)
>> >
>> > Brian Yan
>> >
>> > -------------
>> > This just represents my personal opinion. It doesn't represent my
>> > employer's opinion from any respective.
>> > ----------------
>> >
>> > In article <7qgphk$mjr$1_at_nnrp1.deja.com>,
>> > Todder <toddbrasseur_at_my-deja.com> wrote:
>> > > How do you check data integrity when two columns are related but
>not
>> > in
>> > > the same table?
>> > >
>> > > An example would be having a system which keeps track of
>automobile
>> > > inventory. In one table (Master) you have the serial number,
>> > > manufacturer, etc. In another table (Detail) you have the serial
>> > > number (Foreign Key), color, etc. Each manufacturer has its own
>> list
>> > > of colors that it uses. So when a serial number and color is
>> entered,
>> > > you need to verify that the color is allowed for the manufacturer
>of
>> > > the automobile. My idea was to pass the serial number to a stored
>> > > procedure that finds the manufacturer (from the header) and passes
>> > back
>> > > a list of valid colors.
>> > >
>> > > The check constraint would look like:
>> > >
>> > > CHECK (New.Color IN
>> > > (SELECT Color FROM StoredProcedure(New.SerialNumber))
>> > >
>> > > Is this allowed in ORACLE? The database we are currently using
>> allows
>> > > this to compile but doesn't work.
>> > >
>> > > If not, how would you accomplish this type of check?
>> > >
>> > > Thanks in Advance
>> > >
>> > > Todd
>> > >
>> > > Sent via Deja.com http://www.deja.com/
>> > > Share what you know. Learn what you don't.
>> > >
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Share what you know. Learn what you don't.
>> >
>>
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Sep 01 1999 - 08:18:31 CDT