| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
Sorry, I missed a FROM-clause
Once again:
What about a view
CREATE OR REPLACE VIEW v_actual_thing
as
SELECT thing
FROM
(SELECT thing,
RANK() OVER(PARTITION BY thing ORDER BY version
DESC) AS rk
) v
WHERE rk = 1
to avoid the nasty subselects to get MAX(version)
This works from 8.1.6 on and in EE only.
:-}
On Sat, 9 Feb 2002 13:55:29 +0100, "Peter Laursen" <pl_at_mail1.remove.this.stofanet.dk> wrote:
>"Sean" <sean_at_kewi.net> wrote in message
>news:bc264b7c.0202081333.357ca92f_at_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)
>
>Make another table like
>Table FILE:
>FILE_ID (PK)
>ATTRIBUTES ?Are attributes assigned to a file in general
>then here else in FILE_VERSION
>
>Table FILE_VERSION:
>FILE_ID (PK)
>VERSION (PK)
>(ATTIBUTES only needed if some attributes differ between
>versions)
>
>Table USER_FILE:
>FILE_ID (PK) (FK references table FILE)
>USERNAME(PK)
>
>Now, this solution can sometimes be a pain in a**. I
>designed like this but three levels deep in a schema and
>since nearly all selects relate to the newest VERSION coders
>have to do a lot of subselect where max(version) = ...
>Performence is OK and so is normalization but consider
>moving info about older fileversions to a history table if
>info about old files is rarely used.
>
>Peter Laursen
>
regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de
Received on Sun Feb 10 2002 - 14:02:33 CST
 |
 |