Re: Can ORACLE do this?

In the example, the manufacturer wasn't stored on the detail table (the serial number is).

What I have decided is to use triggers to handle the problem. Sometimes I am able to use subselects to get to the data in other tables.

ie

CHECK (New.Color IN (SELECT Color FROM Table WHERE Manufacturer = (SELECT Manufacturer FROM Master WHERE Serial# = New.Serial#))),

Todd

In article <936192711.1538189548_at_news.pathcom.com>,   Rudy Fernandes <rferdy_at_pathcom.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 10:57:20 CDT