RE: foreign keys on multiple columns

From: Kevin Martin <kevin.martin_at_catapultsystems.com>
Date: Mon, 11 Sep 2000 14:33:06 -0500
Message-Id: <10616.116657@fatcity.com>

Hi Andreas,

The problem with your script is that your FK constraints should reference unique constraints NOT unique indexes. Use the following and everything should work just fine:

create table fahrzeit (

   id                   number(10)                      not null,
   id_log               number(10)                      not null,
   id_spl               number(10)                      not null,
   name                 char(10),

   constraint pk_fahrzeit primary key (id),    constraint unq_fahrzeit UNIQUE (id_spl,id_log)
)

/

create table zug (

   id                   number(10)                      not null,
   id_log               number(10)                      not null,
   id_spl               number(10)                      not null,
   name                 char(10),

   constraint pk_zug primary key (id),
   constraint unq_zug UNIQUE (id_spl,id_log)
)

/

create table fahrzeit2zug (

   id                   number(10)                      not null,
   id_log               number(10),
   id_spl               number(10)                      not null,
   id_zug               number(10),
   id_zug_log           number(10)                      not null,
   id_fahrzeit          number(10),
   id_fahrzeit_log      number(10)                      not null,
   constraint pk_fahrzeit2zug primary key (id),

   constraint fk_key_5_fahrzeit unique (id_spl, id_zug_log),    constraint ak_ak_key_6_fahrzeit_fahrzeit unique (id_spl, id_fahrzeit_log),

   constraint fk_fahrzeit_reference_zug foreign key (id_spl, id_zug_log)
         references zug (id_spl, id_log),
   constraint fk_fahrzeit_reference_fahrzeit foreign key (id_spl,
id_fahrzeit_log)
         references fahrzeit (id_spl, id_log)

)

/

-----Original Message-----
From: Andreas Teich [mailto:teich_at_novedia.de] Sent: Monday, September 11, 2000 10:03 AM To: Multiple recipients of list ORACLE-L Subject: foreign keys on multiple columns

Hi all,

i have some trouble with Oracle 8i (8.1.6) running on NT 4.0. I used the following script to create three tables with some unique indixes and two foreign keys. Everything goes right, until the creation of the foreign key, then i get : ORA-02270: no matching unique or primary key for this column-list. Received on Mon Sep 11 2000 - 14:33:06 CDT