composed foreign key question

From: Moritz Klein <mklein_at_students.uni-mainz.de>
Date: Mon, 19 Sep 2005 15:56:18 +0200
Message-ID: <dgmg1t$coh$1@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 Received on Mon Sep 19 2005 - 08:56:18 CDT