Re: Can ORACLE do this?

Foreign keys need to have the same number of columns with identical datatypes. If manufacturer is not part of the PK, it can not be made part of any FK.

Hth,

--
Sybrand Bakker, Oracle DBA

Rudy Fernandes <rferdy_at_pathcom.com> wrote in message news:936192711.1538189548_at_news.pathcom.com...
> 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 - 10:05:39 CDT