Toad World® Forums

rename of column thats a primary key doesn't include drop index


#1

I renamed a column that happened to be the primary key for my table but the
generated script did not include a drop for the index. When the script was run
as is, the create index step resulted in a -601.

Note the script did not change the constraint name either.

Thanks

Mark Stone

SET SCHEMA = ‘MARK’ ;

SET CURRENT SQLID = ‘MARK’ ;

ALTER TABLE “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”

RENAME COLUMN “STATUS” TO “TRANSACTION_TYPE” ;

ALTER TABLE “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”

DROP PRIMARY KEY ;

CREATE UNIQUE INDEX “FDBEL”.“PK_REG_HISTORY_STATUS”

ON “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”

( TRANSACTION_TYPE ASC )

USING STOGROUP “FSGALLP”

PRIQTY 12 SECQTY 0 ERASE NO

FREEPAGE 0

PCTFREE 10

GBPCACHE CHANGED

DEFINE YES

COMPRESS NO

NOT CLUSTER

NOT PADDED

BUFFERPOOL BP11

CLOSE NO

PIECESIZE 2G

COPY YES ;

ALTER TABLE “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”

ADD CONSTRAINT “STATUS” PRIMARY KEY

(TRANSACTION_TYPE) ;

COMMIT ;

RUNSTATS TABLESPACE FDBEL.FTSREGHS

TABLE (“FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”)

INDEX (FDBEL.PK_REG_HISTORY_STATUS)

UPDATE ALL

HISTORY NONE ;


#2

Message from: jpositano_718

Mark -

I will review this situation further and get back to you.

Jeff


Historical Messages

Author: Jeff Positano
Date: Wed Aug 25 06:52:23 PDT 2010
Mark -

I will review this situation further and get back to you.

Jeff

__

Author: Stone, Mark
Date: Mon Aug 23 14:58:32 PDT 2010
I renamed a column that happened to be the primary key for my table but the
generated script did not include a drop for the index. When the script was run
as is, the create index step resulted in a -601. Note the script did not change
the constraint name either. Thanks Mark Stone SET SCHEMA = ‘MARK’ ; SET CURRENT
SQLID = ‘MARK’ ; ALTER TABLE “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES” RENAME
COLUMN “STATUS” TO “TRANSACTION_TYPE” ; ALTER TABLE
“FDBEL”.“REG_HISTORY_TRANSACTION_TYPES” DROP PRIMARY KEY ; CREATE UNIQUE INDEX
“FDBEL”.“PK_REG_HISTORY_STATUS” ON “FDBEL”.“REG_HISTORY_TRANSACTION_TYPES” (
TRANSACTION_TYPE ASC ) USING STOGROUP “FSGALLP” PRIQTY 12 SECQTY 0 ERASE NO
FREEPAGE 0 PCTFREE 10 GBPCACHE CHANGED DEFINE YES COMPRESS NO NOT CLUSTER NOT
PADDED BUFFERPOOL BP11 CLOSE NO PIECESIZE 2G COPY YES ; ALTER TABLE
“FDBEL”.“REG_HISTORY_TRANSACTION_TYPES” ADD CONSTRAINT “STATUS” PRIMARY KEY
(TRANSACTION_TYPE) ; COMMIT ; RUNSTATS TABLESPACE FDBEL.FTSREGHS TABLE
(“FDBEL”.“REG_HISTORY_TRANSACTION_TYPES”) INDEX (FDBEL.PK_REG_HISTORY_STATUS)
UPDATE ALL HISTORY NONE ;
__



#3

Mark -

I will review this situation further and get back to you.

Jeff