Re: composed foreign key question

"Moritz Klein" <mklein_at_students.uni-mainz.de> wrote in message news:dgmg1t$coh$1_at_news1.zdv.uni-mainz.de...
> Hi NG,
> same data different problem.
> Plattform: Windows 2003 Server
> Oracle 10.1.0.3
> parent-table:
> create table underlying (
> contract date,
> lookupdate date,
> opening number,
> settle number,
> change number,
> daily_high number,
> daily_high_type char(1),
> daily_low number,
> daily_low_type char(1),
> lifetime_high number,
> lifetime_high_type char(1),
> lifetime_low number,
> lifetime_low_type char(1),
> closing number,
> connect_vol number,
> basis_vol number,
> trade_vol number,
> official_vol_prev number,
> open_int_prev number,
> open_int_change_prev number,
> atm_vola number,
> expiry number) tablespace finance;
> alter table underlying add (
> contraint pk_underlying primary key (contract, lookupdate);
> /
>
> child-table:
> create table option_prices (
> contract date,
> lookupdate date,
> call_settle number,
> call_vola number,
> call_delta number,
> strike number,
> put_settle number,
> put_vola number,
> put_delta number
> ) tablespace finance;
>
> That's the situation so far. Now I want to create a foreign key to keep
> referential integrity. The problem is I can't create a foreign key on the
> combination of to columns in the child- _and_ the parent-table. I thought
> of creating a foreign keys for every one of the two columns, but this
> would allow data to be inserted that cannot be referenced to the
> parent-table. e.g. combination of two dates from which each is in the
> parent-table but not in the particular composition. So for now I'm
> thinking of dropping the old primary key an constructing a generic primary
> key. The question is: Are there other possibilities, as I don't think it
> is good design, to generate an artificial primary key when I have an
> natural one.
>
> Any help appriciated,
> Moritz

you're likely to spark a religious debate on this one on the merits of natural keys vs surrocate (system-assigned) PKs

that aside, the first big question is why can't you create a 2-column FK on your option_prices table? why doesn't the following SQL work on your system?

    alter table option_prices add constraint option_prices$underlying#fk     foreign key (contract,lookupdate)
    references underlying

however, there are a few things suspicious (or just missing) about your design:

[_] if underlying has a two-column PK, the CONTRACT column should be an FK, presumably to a CONTRACT table
[_] CONTRACT is a poorly named column, as it does not contain a contract, but appears to reference only a date related to a contract [_] the design not only implies a CONTRACT table, but implies that there can only be one CONTRACT per day (or, taken to the extreme, per second) [_] a similar implication apples to the UNDERLYING table (a very ambiguously names table); by using a date as the 2nd element of the PK, are you implying a maximum of one record per contract per day? per hour? per minute second?

suggestions:
[_] correctly model the CONTRACT table, likely with a system-assigned (SA) PK
[_] define the UNDERLYING table's purpose and actual relationship to the CONTRACT table, and give it a more appropriate name. if it is a true dependent table, determine what would be most appropriate for the 2nd element of the PK -- likely it will also be a system-assigned ID of some sort. if it is not a true dependent table (ie, records can be transferred from one contract to another) then likely it should have a single-column SA PK
[_] similar issues with the option_prices table. i'm guessing this is a true dependent of whatever UNDERLYING represents, so it will likely have a multi-part PK. just determine what is best to use for the non-FK column of the PK, again, likely a SA ID of some sort

++mcs Received on Mon Sep 19 2005 - 09:58:47 CDT