Re: How to make a Foreign key in this scenario

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