| 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
Teresita,
You cannot create a unique index on the combination of columns that you listed because there are more than one of those type of records.
You can see the problem if you do the following:
select distinct COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM, count(*)
from OEINVCLINE
group by COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM
having count(*) > 1
This will list the records showing you that you have more than one record with that combination of columns. You could create a normal (non-unique) index on those columns if that would help you.
Good Luck
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, September 29, 2003 3:35 PM
To: Multiple recipients of list ORACLE-L
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
TABLESPACE LAWSON_PRUEBAS_INDICES PCTFREE 5
STORAGE ( INITIAL 40960 ))
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: 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 Mon Sep 29 2003 - 16:14:43 CDT
 |
 |