Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to make a Foreign key in this scenario
Exactly what I pointed out in my initial response. A bad design is a bad
design. Writing a ton of code around it to make up for its inadequacies and
errors is the wrong approach.
A many-to-many relationship is a bad thing. Fix the bad thing ... don't just try to paper over it.
Daniel Morgan
Sybrand Bakker wrote:
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3C644689.3FC9_at_yahoo.com...
> > Sean wrote:
> > >
> > > Trying to make a many to many table relationship. One of our tables
> > > contains files with versions (like 1, 2, 3, etc). We want to then tie
> > > the access to the file based on a user's username (hence the many to
> > > many table) and not a version in particular, but the file in general.
> > > The caveat here is that we can not make a FK from Table 1 to Table 3
> > > because we're not bringing the entire Primary key from Table 1 over.
> > > You'll get a "ORA-02270: no matching unique or primary key for this
> > > column-list" error.
> > >
> > > Is there a way to enforce integrity in Table 3 making sure there is a
> > > matching value in Table 1 ? (without the version column of course).
> > >
> > > Table 1
> > > --------------
> > > FILE_ID (PK)
> > > VERSION (PK)
> > > ATTRIBUTES
> > >
> > > Table 2
> > > --------------
> > > USERNAME (PK)
> > > ATTRIBUTES
> > >
> > > Table 3 (the many to many)
> > > --------------
> > > FILE_ID (?????)
> > > USERNAME (FK)
> >
> > A trigger should do the trick..
> >
> > hth
> > connor
> > --
> > ==============================
> > Connor McDonald
> >
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue..."
>
>
>