RE: Issue:RE: ORA-02270: no matching unique or primary key for th

[Khedr, Waleed]

You have the unique index defined as global index so my guess any partition operation (parallel load, etc) will disable the index.

-----Original Message-----
From: laura pena [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 2:14 PM
To: Multiple recipients of list ORACLE-L
Subject: Issue:RE: ORA-02270: no matching unique or primary key for this column-

Seems this table cdr_table has a disabled constraint:

  1  SELECT constraint_name, constraint_type, status, deferrable, deferred FROM
  2* user_constraints where table_name='CDR_TABLE'

 

CONSTRAINT_NAME                C STATUS   DEFERRABLE     DEFERRED
------------------------------ - -------- -------------- ---------
SYS_C005049                    C ENABLED  NOT DEFERRABLE IMMEDIATE
SYS_C005050                    C ENABLED  NOT DEFERRABLE IMMEDIATE
SYS_C005051                    C ENABLED  NOT DEFERRABLE IMMEDIATE
PK_CDR_TABLE                   P DISABLED NOT DEFERRABLE IMMEDIATE

 

So my question now is does anyone know who you get into a disabled state? Since my creation statements for this primary key, I did put to disable...

 

puzzled.

 

Many Thanks,

-Lizz

"Khedr, Waleed" <[EMAIL PROTECTED]> wrote:

I ran the same ddl in 9.2 and had no problems. Check for tables having the same names in other schemas

 

Waleed

-----Original Message-----
From: laura pena [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2003 11:19 AM
To: Multiple recipients of list ORACLE-L
Subject: ORA-02270: no matching unique or primary key for this column-list -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 New Yahoo! Shopping - with improved product search

---

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