| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problems creading a Index
The duplicate keys message is stating that there are multiple rows
containing the same values for the specified elements in your index
(COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM) thus the index can not be a unique
index.
-----Original Message-----
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
Teresita Castro
Sent: Monday, September 29, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L
Subject: Problems creading a Index
HI!!
I want to create the next index:
CREATE UNIQUE INDEX LAWSON2.IOEINVCLINE1 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 )
But I can't because Oracle send me the next error:
The following error has occurred:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
I checked on TOAD ( with F4 on the table name) and It give me the next
script.
I don't have an index with the field ITEM on it, so I don't undestand
what I am getting this error.
DROP TABLE OEINVCLINE CASCADE CONSTRAINTS ; CREATE TABLE OEINVCLINE (
COMPANY NUMBER (4) NOT NULL,
INVC_PREFIX CHAR (2) NOT NULL,
INVC_NUMBER NUMBER (8) NOT NULL,
LINE_NBR NUMBER (6) NOT NULL,
LINE_TYPE CHAR (1) NOT NULL,
ITEM CHAR (32) NOT NULL,
DESCRIPTION CHAR (30) NOT NULL,
ORDER_NBR NUMBER (8) NOT NULL,
SHIPMENT_NBR NUMBER (10) NOT NULL,
QUANTITY NUMBER (13,4) NOT NULL,
INVC_CW_QTY NUMBER (13,4) NOT NULL,
SPR_UOM CHAR (4) NOT NULL,
SELL_UOM CHAR (4) NOT NULL,
SEC_UOM CHAR (4) NOT NULL,
MULT_SPR_FL CHAR (1) NOT NULL,
SPR_TO_STOCK NUMBER (13,7) NOT NULL,
SELL_TO_STOCK NUMBER (13,7) NOT NULL,
SEC_UOM_MULT NUMBER (13,7) NOT NULL,
LOCATION CHAR (5) NOT NULL,
PRICE_STATUS CHAR (1) NOT NULL,
ENTERED_PRICE NUMBER (13,5) NOT NULL,
UNIT_PRICE NUMBER (13,5) NOT NULL,
SELL_PRC_CURR NUMBER (15,7) NOT NULL,
SELL_UNIT_PRC NUMBER (15,7) NOT NULL,
UNIT_COST NUMBER (13,5) NOT NULL,
CURRENT_COST NUMBER (13,5) NOT NULL,
NO_CHARGE_FL CHAR (1) NOT NULL,
ENTERED_DISC NUMBER (15,2) NOT NULL,
ADD_ON_DISC NUMBER (15,2) NOT NULL,
ALLOC_DISC NUMBER (15,2) NOT NULL,
TAX_EXEMPT_CD CHAR (1) NOT NULL,
TAX_CODE CHAR (10) NOT NULL,
ENT_TAXABLE NUMBER (15,2) NOT NULL,
TAXABLE_BSE NUMBER (15,2) NOT NULL,
TAX_AMT_CURR NUMBER (15,2) NOT NULL,
TAX_AMT_BSE NUMBER (15,2) NOT NULL,
REASON_CODE CHAR (4) NOT NULL,
DISC_CODE CHAR (10) NOT NULL,
ORD_DISC_FL CHAR (1) NOT NULL,
CONTRACT_NBR CHAR (14) NOT NULL,
PROMOTION CHAR (10) NOT NULL,
ACTIVITY CHAR (15) NOT NULL,
ACCT_CATEGORY CHAR (5) NOT NULL,
ATN_OBJ_ID NUMBER (12) NOT NULL,
ACTIVITY_C CHAR (15) NOT NULL,
ACCT_CATEG_C CHAR (5) NOT NULL,
ATN_OBJ_ID_C NUMBER (12) NOT NULL,
FINAL_INVC_FL CHAR (1) NOT NULL,
SLS_ACCT_UNIT CHAR (15) NOT NULL,
SLS_ACCOUNT NUMBER (6) NOT NULL,
SLS_SUB_ACCT NUMBER (4) NOT NULL,
SALES_MAJCL CHAR (4) NOT NULL,
SALES_MINCL CHAR (4) NOT NULL,
DSC_AMT_01 NUMBER (15,2) NOT NULL,
DSC_AMT_02 NUMBER (15,2) NOT NULL,
DSC_AMT_03 NUMBER (15,2) NOT NULL,
DSC_ACCT_UNIT_01 CHAR (15) NOT NULL,
DSC_ACCT_UNIT_02 CHAR (15) NOT NULL,
DSC_ACCT_UNIT_03 CHAR (15) NOT NULL,
DSC_ACCOUNT_01 NUMBER (6) NOT NULL,
DSC_ACCOUNT_02 NUMBER (6) NOT NULL,
DSC_ACCOUNT_03 NUMBER (6) NOT NULL,
DSC_SUB_ACCT_01 NUMBER (4) NOT NULL,
DSC_SUB_ACCT_02 NUMBER (4) NOT NULL,
DSC_SUB_ACCT_03 NUMBER (4) NOT NULL,
DSC_AMT_BASE NUMBER (15,2) NOT NULL,
OFF_ACCT_UNIT CHAR (15) NOT NULL,
OFF_ACCOUNT NUMBER (6) NOT NULL,
OFF_SUB_ACCT NUMBER (4) NOT NULL,
CGS_ACCT_UNIT CHAR (15) NOT NULL,
CGS_ACCOUNT NUMBER (6) NOT NULL,
CGS_SUB_ACCT NUMBER (4) NOT NULL,
LAST_MISC_SEQ NUMBER (3) NOT NULL,
LAST_COMM_SEQ NUMBER (3) NOT NULL,
TERRITORY CHAR (4) NOT NULL,
SALESMAN NUMBER (4) NOT NULL,
SALESMAN_2 NUMBER (4) NOT NULL,
COMM_RATE_1 NUMBER (7,7) NOT NULL,
COMM_RATE_2 NUMBER (7,7) NOT NULL,
COMM_SPLIT NUMBER (5,5) NOT NULL,
USER_FLD1 CHAR (2) NOT NULL,
USER_FLD2 CHAR (30) NOT NULL,
USER_FLD3 CHAR (15) NOT NULL,
LINE_GRS_CURR NUMBER (15,2) NOT NULL,
INVL_OBJ_ID NUMBER (12) NOT NULL,
PROD_TAX_CAT CHAR (15) NOT NULL,
LINE_GRS_BASE NUMBER (15,2) NOT NULL,
OILSET2_SS_SW CHAR (1) NOT NULL,
OILSET3_SS_SW CHAR (1) NOT NULL,
OILSET4_SS_SW CHAR (1) NOT NULL,
CONSTRAINT OILSET1
USING INDEX
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5
STORAGE ( INITIAL 40960 ))
TABLESPACE LAWSON_PRUEBAS_DATOS
PCTFREE 5
PCTUSED 94
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1024000
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
CREATE INDEX LAWSON2.OILSET2 ON
"LAWSON2".OEINVCLINE(OILSET2_SS_SW, COMPANY, INVC_PREFIX, INVC_NUMBER,
SALES_MAJCL, SALES_MINCL, LINE_NBR)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 61440 )
;
CREATE INDEX LAWSON2.OILSET3 ON
"LAWSON2".OEINVCLINE(OILSET3_SS_SW, ATN_OBJ_ID)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 )
;
CREATE INDEX LAWSON2.OILSET4 ON
"LAWSON2".OEINVCLINE(OILSET4_SS_SW, ATN_OBJ_ID_C)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 )
;
CREATE UNIQUE INDEX LAWSON2.OILSET5 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, INVC_NUMBER, TAX_CODE,
PROD_TAX_CAT, LINE_NBR)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 81920 )
;
CREATE UNIQUE INDEX LAWSON2.OILSET6 ON
"LAWSON2".OEINVCLINE(COMPANY, INVC_PREFIX, LINE_NBR, INVC_NUMBER)
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5 STORAGE(INITIAL 40960 )
;
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Blake INET: jblake_at_arrow.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 Thu Oct 02 2003 - 08:59:35 CDT
 |
 |