foreign keys on multiple columns

From: Andreas Teich <teich_at_novedia.de>
Date: Mon, 11 Sep 2000 13:41:36 +0200
Message-Id: <10616.116620@fatcity.com>

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.

Does anybody have "any" idea, whats going wrong ??

TIA
/*==============================================================*/
/* Database name: MODL_1 */
/* DBMS name: ORACLE Version 8i */
/* Created on: 9/11/00 1:31:27 PM */
/*==============================================================*/

/*==============================================================*/
/* Table : fahrzeit */
/*==============================================================*/

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) )
/

/*==============================================================*/
/* Index: unq_fahrzeit */
/*==============================================================*/
create unique index unq_fahrzeit on fahrzeit (

   id_spl asc,
   id_log asc
)
/

/*==============================================================*/
/* Table : zug */
/*==============================================================*/

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)
)
/

/*==============================================================*/
/* Index: unq_zug */
/*==============================================================*/
create unique index unq_zug on zug (

   id_spl asc,
   id_log asc
)
/

/*==============================================================*/
/* Table : fahrzeit2zug */
/*==============================================================*/

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)

)
/

Novedia AG, Andreas Teich, Dipl.-Math.
Hardenbergstrasse 19 tel:+49.30.311889-55 fax:+49.30.311889-19 Received on Mon Sep 11 2000 - 06:41:36 CDT