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

Home -> Community -> Mailing Lists -> Oracle-L -> Weird pk index, please illuminate !

Weird pk index, please illuminate !

From: Carmen Rusu <crusu_at_clearcommerce.com>
Date: Thu, 17 Aug 2000 09:33:59 -0500
Message-Id: <10592.114846@fatcity.com>


Hi Oracle gurus:

I have inherited from the previous DBA a strange schema. It was created with ERwin. Each table looks like the one below. First is the table definition. Then an unique NAMED index is created on the intended primary key columns. Then a system default named (!!!) primary key constraint is added via alter table. Finally more system default named constraints are added via alter table, namely foreign keys.

I do plan to replace these system default named keys with something like "table_name_pkn" or "table_name_fkn". But I am puzzled about this primary key index.

What is the advantage of having this NAMED primary key index ?

So far I see only the disadvantage of maintaining another index. I know that Oracle will create a unique index for each primary key you define and you dont need to know its name.

Here's an example:

CREATE TABLE dgtlrcpt_audit (

       cc_client_id         NUMBER(9) NOT NULL,
       ord_id               VARCHAR2(36) NOT NULL,
       audit_dttm           DATE NOT NULL,
       email_to             VARCHAR2(128) NULL,
       svr_ret_cd           NUMBER(3) NULL,
       email_from           VARCHAR2(64) NULL,
       template_type        NUMBER(3) NOT NULL,
       retry_cd             NUMBER(1) NULL,
       server_name          VARCHAR2(64) NULL,
       email_proc_cd        NUMBER(1) NOT NULL,
       email_file_nm        VARCHAR2(260) NULL)
       TABLESPACE digital_tab
       STORAGE (
              INITIAL 64m
              NEXT 64m
              PCTINCREASE 0);

CREATE UNIQUE INDEX XPKdgtlrcpt_audit ON dgtlrcpt_audit

*************************************** this is the one!!???
****************************
named*******************************************
(
       cc_client_id                   ASC,
       ord_id                         ASC,
       audit_dttm                     ASC
)     TABLESPACE digital_idx
       STORAGE (
              INITIAL 64m
              NEXT 64m
              PCTINCREASE 0 );

ALTER TABLE dgtlrcpt_audit
       ADD  ( PRIMARY KEY (cc_client_id, ord_id, audit_dttm)
       ******************system default name !!*************************
             USING INDEX
              TABLESPACE digital_idx
              STORAGE (
                     INITIAL 64m
                     NEXT 64m
                     PCTINCREASE 0 ) ) ;

ALTER TABLE dgtlrcpt_audit

*****************************system default name !!*************************
       ADD  ( FOREIGN KEY (cc_client_id)
                             REFERENCES cc_client ) ;
Received on Thu Aug 17 2000 - 09:33:59 CDT

Original text of this message

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