Re: design problem with fk part of pk in oracle env

On Tue, 06 May 2003 15:35:57 +0000, osy45 <member18536_at_dbforums.com> wrote:

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

The error message says it all. You need to have ID_RIC_REFERENZZINS declared as the Primary Key of RIC_REFERENZZINS.

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

PO Box 720
Belconnen ACT 2616 Australia
+61+2+ 62541341 (h)
  0427 541344 (mobile) Received on Tue May 06 2003 - 23:39:49 CEST