How to make a Foreign key in this scenario

From: Sean <sean_at_kewi.net>
Date: 8 Feb 2002 13:33:04 -0800
Message-ID: <bc264b7c.0202081333.357ca92f@posting.google.com>

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 - 15:33:04 CST