Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: foreign keys on multiple columns
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C01C3E.CAB8967E
Content-Type: text/plain;
charset="iso-8859-1"
change the unique index creations as follows....
create table fahrzeit (
id number(10) not null, id_log number(10) not null, id_spl number(10) not null, name char(10),
-----Original Message-----
From: Andreas Teich [mailto:teich_at_novedia.de]
Sent: Monday, September 11, 2000 11: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.
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),
/*==============================================================*/
/* 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),
/*==============================================================*/
/* 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 D-10623 Berlin, Germany mailto:teich@novedia.de http://novedia.de/
-- Author: Andreas Teich INET: teich_at_novedia.de Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C01C3E.CAB8967E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>RE: foreign keys on multiple columns</TITLE> </HEAD> <BODY> <P><FONT SIZE=3D2>change the unique index creations as = follows....</FONT> </P> <BR> <P><FONT SIZE=3D2>create table fahrzeit (</FONT> <BR><FONT SIZE=3D2> = id &nbs= p; = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_log = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_spl = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = name &n= bsp; char(10),</FONT> <BR><FONT SIZE=3D2> constraint pk_fahrzeit primary key = (id),</FONT> <BR><FONT SIZE=3D2> constraint unq_fahrzeit unique = (id_spl, id_log ))</FONT> </P> <BR> <BR> <P><FONT SIZE=3D2>-----Original Message-----</FONT> <BR><FONT SIZE=3D2>From: Andreas Teich [<A = HREF=3D"mailto:teich_at_novedia.de">mailto:teich_at_novedia.de</A>]</FONT> <BR><FONT SIZE=3D2>Sent: Monday, September 11, 2000 11:03 AM</FONT> <BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT SIZE=3D2>Subject: foreign keys on multiple columns</FONT> </P> <BR> <P><FONT SIZE=3D2>Hi all,</FONT> </P> <P><FONT SIZE=3D2>i have some trouble with Oracle 8i (8.1.6) running on = NT 4.0. I used the</FONT> <BR><FONT SIZE=3D2>following script to create three tables with some = unique indixes and two</FONT> <BR><FONT SIZE=3D2>foreign keys. Everything goes right, until the = creation of the foreign key,</FONT> <BR><FONT SIZE=3D2>then i get : ORA-02270: no matching unique or = primary key for this</FONT> <BR><FONT SIZE=3D2>column-list.</FONT> </P> <P><FONT SIZE=3D2>Does anybody have "any" idea, whats going = wrong ??</FONT> </P> <P><FONT SIZE=3D2>TIA</FONT> </P> <BR> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Database name: = MODL_1 = = = */</FONT> <BR><FONT SIZE=3D2>/* DBMS name: ORACLE = Version = 8i &nbs= p; &nbs= p; */</FONT> <BR><FONT SIZE=3D2>/* Created on: 9/11/00 = 1:31:27 = PM &nbs= p; &nbs= p; */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> </P> <BR> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Table : = fahrzeit &nbs= p; &nbs= p; &nbs= p; */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> </P> <P><FONT SIZE=3D2>create table fahrzeit (</FONT> <BR><FONT SIZE=3D2> = id &nbs= p; = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_log = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_spl = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = name &n= bsp; char(10),</FONT> <BR><FONT SIZE=3D2> constraint pk_fahrzeit primary key = (id)</FONT> <BR><FONT SIZE=3D2>)</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Index: = unq_fahrzeit = = = */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>create unique index unq_fahrzeit on fahrzeit = (</FONT> <BR><FONT SIZE=3D2> id_spl asc,</FONT> <BR><FONT SIZE=3D2> id_log asc</FONT> <BR><FONT SIZE=3D2>)</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Table : = zug &nb= sp; &nb= sp; &nb= sp; &nb= sp; */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> </P> <P><FONT SIZE=3D2>create table zug (</FONT> <BR><FONT SIZE=3D2> = id &nbs= p; = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_log = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_spl = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = name &n= bsp; char(10),</FONT> <BR><FONT SIZE=3D2> constraint pk_zug primary key = (id)</FONT> <BR><FONT SIZE=3D2>)</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Index: = unq_zug  = ;  = ;  = ; = */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>create unique index unq_zug on zug (</FONT> <BR><FONT SIZE=3D2> id_spl asc,</FONT> <BR><FONT SIZE=3D2> id_log asc</FONT> <BR><FONT SIZE=3D2>)</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <BR> <P><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> <BR><FONT SIZE=3D2>/* Table : = fahrzeit2zug = = = */</FONT> <BR><FONT = SIZE=3D2>/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D*/</FONT> </P> <P><FONT SIZE=3D2>create table fahrzeit2zug (</FONT> <BR><FONT SIZE=3D2> = id &nbs= p; = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_log = number(10),</FONT> <BR><FONT SIZE=3D2> = id_spl = = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_zug = number(10),</FONT> <BR><FONT SIZE=3D2> = id_zug_log = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> = id_fahrzeit = number(10),</FONT> <BR><FONT SIZE=3D2> = id_fahrzeit_log = number(10) &n= bsp; not = null,</FONT> <BR><FONT SIZE=3D2> constraint pk_fahrzeit2zug primary key = (id),</FONT> <BR><FONT SIZE=3D2> constraint fk_key_5_fahrzeit unique = (id_spl, id_zug_log),</FONT> <BR><FONT SIZE=3D2> constraint = ak_ak_key_6_fahrzeit_fahrzeit unique (id_spl,id_fahrzeit_log),</FONT> <BR><FONT SIZE=3D2> constraint fk_fahrzeit_reference_zug = foreign key (id_spl, id_zug_log)</FONT> <BR><FONT SIZE=3D2> = references zug (id_spl, id_log),</FONT> <BR><FONT SIZE=3D2> constraint = fk_fahrzeit_reference_fahrzeit foreign key = (id_spl,id_fahrzeit_log)</FONT> <BR><FONT SIZE=3D2> = references fahrzeit (id_spl, id_log)</FONT> <BR><FONT SIZE=3D2>)</FONT> <BR><FONT SIZE=3D2>/</FONT> </P> <BR> <BR> <BR> <BR> <BR> <BR> <P><FONT SIZE=3D2>Novedia AG, Andreas Teich, Dipl.-Math.</FONT> <BR><FONT SIZE=3D2>Hardenbergstrasse = 19 = tel:+49.30.311889-55 fax:+49.30.311889-19</FONT> <BR><FONT SIZE=3D2>D-10623 Berlin, Germany = <A = HREF=3D"mailto:teich_at_novedia.de">mailto:teich_at_novedia.de</A> <A = HREF=3D"http://novedia.de/" = TARGET=3D"_blank">http://novedia.de/</A></FONT> </P> <BR> <P><FONT SIZE=3D2>-- </FONT> <BR><FONT SIZE=3D2>Author: Andreas Teich</FONT> <BR><FONT SIZE=3D2> INET: teich_at_novedia.de</FONT> </P> <P><FONT SIZE=3D2>Fat City Network Services -- (858) = 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT SIZE=3D2>San Diego, = California -- Public Internet = access / Mailing Lists</FONT> <BR><FONT = SIZE=3D2>---------------------------------------------------------------= -----</FONT> <BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =Received on Mon Sep 11 2000 - 17:22:30 CDT
![]() |
![]() |