Re: How to make a Foreign key in this scenario

Your entire problem is defined in your very first sentence. Many to many relationships are a violation of relational principles.

Make an intersecting entity ... a table that contains only the primary keys of the two tables you are attempting to join.

Then you will not only have a normalized database ... you will be able to create foreign keys.

Daniel Morgan

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)
Received on Fri Feb 08 2002 - 18:11:44 CST