Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    833

    Unanswered: stange behavior of oracle while creating a reference between tables ORA-02270 thrown

    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

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

    Thanks

    osy45

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: stange behavior of oracle while creating a reference between tables ORA-02270 thrown

    There are two problems with your design:

    1) You have declared a UNIQUE index, but not a PRIMARY or UNIQUE constraint on RIC_REFERENZZINS. An index alone is not enough.

    2) Your UNIQUE index is on 2 columns: ID_RIC_REFERENZZINS and UPDATE_NO, but in ZINSSATZ you are trying to create a foreign key based on ID_RIC_REFERENZZINS only. The columns of the foreign key must match the primary/unique key being referenced.

    If this works in SYBASE, it suggests SYBASE is faulty!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •