Re: How to make a Foreign key in this scenario

"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..."

<soapbox mode on>
Generally speaking it is a bad idea to write triggers to make up for a flawed datamodel. Sooner or later you will suffer from the consequences as you will need more and more of those 'tricks' <soapbox mode off>

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address