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
)
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-----
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 DBHelp ....I am getting the following error on a 9.2.0.3 RAC on Solaris DB:Any help would be greatly appreciated THanks -LizzSQL> 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-list1>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 tableCREATE 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 system3> Here is how I created indexes and primary keysCREATE 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