Oracle-L: ORA-02270: no matching unique or primary key for this column-list -9iR2 DB

From: laura pena <lizzpenaorclgrp_at_yahoo.com>
Date: Wed, 15 Oct 2003 07:19:29 -0800
Message-ID: <F001.005D33D2.20031015071929@fatcity.com>

Help ....I am getting the following error on a 9.2.0.3 RAC on Solaris DB: Any help would be greatly appreciated THanks -Lizz

SQL> ALTER TABLE AE ADD (
  CONSTRAINT FK_AE FOREIGN KEY (CDR_ID)
    REFERENCES CDR_TABLE (CALL_ID))
/

  2 3 4 ALTER TABLE AE ADD (
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

1>Here is how I created both tables:

CREATE TABLE VOICELOG.AE
(

  CDR_ID      VARCHAR2(16)                      NOT NULL,
  AE_SEQ_ID   NUMBER(5)                         NOT NULL,
  AE_FILE     VARCHAR2(64)                      NOT NULL,
  AE_TYPE     VARCHAR2(1)                       NOT NULL,
  AUDIO_TYPE  VARCHAR2(6)                       NOT NULL,
  AE_TEXT     VARCHAR2(255)

)
TABLESPACE DYNDATA_EXLG
PCTFREE 25
INITRANS 4
STORAGE (

                                MAXEXTENTS UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

LOGGING
NOCACHE
NOPARALLEL
/

REM This is a compsite partitioned table

CREATE TABLE VOICELOG.CDR_TABLE
(

  CALL_ID             VARCHAR2(16)              NOT NULL,
  CDR_START_DT   DATE                           NOT NULL,
  CDR_DNIS            VARCHAR2(20)              NOT NULL,
  CDR_ANI             VARCHAR2(20)              NOT NULL

)
TABLESPACE DYNDATAP1_M
PCTFREE 10
INITRANS 2
STORAGE (

            MAXEXTENTS UNLIMITED
            PCTINCREASE      0
           )

PARTITION BY RANGE (CDR_START_DT)
SUBPARTITION BY HASH(CDR_START_DT) SUBPARTITIONS 4 STORE IN (DYNDATAP1_LG ,DYNDATAP2_LG,DYNDATAP1_LG,DYNDATAP2_LG) (
PARTITION p_111998 VALUES LESS THAN (TO_DATE('111998','MMYYYY'))

                SUBPARTITIONS 4 STORE IN  (DYNDATAP1_LG, DYNDATAP2_LG,DYNDATAP1_

LG, DYNDATAP2_LG),
.....

2> Loaded data in via import from an 8.1.7 system

3> Here is how I created indexes and primary keys CREATE UNIQUE INDEX PK_AE ON AE
(CDR_ID, AE_SEQ_ID)
NOLOGGING
TABLESPACE STATICIDX_EXLG
PCTFREE 10
INITRANS 2
STORAGE (

             MAXEXTENTS UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL
/

ALTER TABLE AE ADD (
  CONSTRAINT PK_AE PRIMARY KEY (CDR_ID, AE_SEQ_ID)     USING INDEX
    TABLESPACE STATICIDX_EXLG
    PCTFREE 10
    INITRANS 2
    STORAGE (

                MAXEXTENTS UNLIMITED
                PCTINCREASE      0
               ))

CREATE UNIQUE INDEX PK_CDR_TABLE ON CDR_TABLE (CALL_ID)
NOLOGGING
TABLESPACE DYNIDXP1_LG
PCTFREE 15
INITRANS 2
STORAGE (

            MAXEXTENTS UNLIMITED
            PCTINCREASE  0
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL
/

ALTER TABLE CDR_TABLE ADD CONSTRAINT
PK_CDR_TABLE PRIMARY KEY (CALL_ID) USING INDEX
/

Thanks in advance.
-Lizz

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: laura pena
  INET: lizzpenaorclgrp_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

Received on Wed Oct 15 2003 - 10:19:29 CDT