Oracle-L: RE: ORA-02270: no matching unique or primary key for this column-

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 15 Oct 2003 07:39:25 -0800
Message-ID: <F001.005D33D3.20031015073925@fatcity.com>

Lizz,

Are you sure that the PK was created on the CDR_TABLE? It looks like it might fail because you first created a unique index on the table, then a PK and used the USING INDEX clause. I would think that the PK would not be created because you cannot have two unique indexes on the same column.

Try dropping the PK_CDR_TABLE index, and create the PK with the using clause like this:

ALTER TABLE CDR_TABLE ADD CONSTRAINT
PK_CDR_TABLE PRIMARY KEY (CALL_ID) USING INDEX
TABLESPACE DYNIDXP1_LG
PCTFREE 15
INITRANS 2
STORAGE (

            MAXEXTENTS UNLIMITED
            PCTINCREASE  0
            BUFFER_POOL      DEFAULT
           )

It will create the same index for you.

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, October 15, 2003 11:19 AM To: Multiple recipients of list ORACLE-L -9iR2 DB

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
<http://shopping.yahoo.com/?__yltc=s%3A150000443%2Cd%3A22708228%2Cslk%3Atext %2Csec%3Amail> New Yahoo! Shopping - with improved product search

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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:39:25 CDT