Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problems creading a Index

RE: Problems creading a Index

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Mon, 29 Sep 2003 14:14:52 -0800
Message-ID: <F001.005D1662.20030929141452@fatcity.com>


Yes was the unique, thanks to all!!

>>> Brad.Odland_at_qtiworld.com 09/29/03 03:09PM >>>

 the first three items in the primary key index is the same as the first items in your UNIQUE index you are trying to make...would be my guess....

don't make a unique index

-----Original Message-----
Sent: Monday, September 29, 2003 2: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
  PRIMARY KEY ( COMPANY, INVC_PREFIX, INVC_NUMBER, LINE_NBR )     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: Teresita Castro
  INET: Teresita.Castro_at_s-martmx.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 Mon Sep 29 2003 - 17:14:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US