design problem with fk part of pk in oracle env

From: osy45 <member18536_at_dbforums.com>
Date: Tue, 06 May 2003 15:35:57 +0000
Message-ID: <2845288.1052235357@dbforums.com>

hi folks,

I've designed some tables and a relationship between them

/ *=================================================

=============*/

/* Table: RIC_REFERENZZINS */

/ *=================================================

=============*/

create table RIC_REFERENZZINS (
ID_RIC_REFERENZZINS INTEGER not null,
UPDATE_NO NUMBER(5) not null,
ID_REFERENZZINS INTEGER not null,
RIC_FREQUENZ INTEGER not null,

FREQUENZ_EINHEIT VARCHAR2(3),
ZEITRAUM_AB DATE,
ZEITRAUM_BIS DATE,

ID_DATENANBIETER INTEGER not null,
ID_MARKTPLATZ INTEGER not null,
USER_IK NUMBER(5),
REC_STATUS VARCHAR2(1),
VALID_FROM DATE,
VALID_TO DATE
)
/

/ *=================================================

=============*/

/* Index: RIC_REFERENZZINS_PK */

/ *=================================================

=============*/

create unique index RIC_REFERENZZINS_PK on RIC_REFERENZZINS ( ID_RIC_REFERENZZINS ASC,
UPDATE_NO ASC
)
/

create table ZINSSATZ (
ID_ZINSSATZ INTEGER not null,
UPDATE_NO NUMBER(5) not null,
ZINSSATZ NUMBER(9,6),
QUOTIERUNGSZEIT DATE,
UEBERNAHMEZEIT DATE,
ID_KURSART INTEGER not null,
ID_RIC_REFERENZZINS INTEGER not null,
USER_IK NUMBER(5),
REC_STATUS VARCHAR2(1),
VALID_FROM DATE,
VALID_TO DATE
)
/

/ *=================================================

=============*/

/* Index: ZINSSATZ_PK */

/ *=================================================

=============*/

create unique index ZINSSATZ_PK on ZINSSATZ ( ID_ZINSSATZ ASC,
UPDATE_NO ASC
)
/

alter table ZINSSATZ
add constraint FK_ZINSSATZ_RELATIONS_RIC_REFE foreign key (ID_RIC_REFERENZZINS)
references RIC_REFERENZZINS (ID_RIC_REFERENZZINS) /

• ****************** Error ************************************** --
• references RIC_REFERENZZINS (ID_RIC_REFERENZZINS)
• *
• ERROR at line 3:
• ORA-02270: no matching unique or primary key for this column-list

how to work around it in an Oracle enviroment as of 8.1.7.4 on Sun Solaris 2.8

table zinsatz
id_zinssatz is incremented by a sequence and for id_zinssatz there several updates though in the referenced table I only want the id_zinssatz referenced ...

p.s. as for sybase design it works properly but not for ORCL why???

any help highly appreciated
Thanks

--
Posted via http://dbforums.com

Received on Tue May 06 2003 - 10:35:57 CDT