Re: Weird pk index, please illuminate !

Carmen,

I also use ERwin and do not have the constraint names generated by Oracle. For example, my PK constraint is called "EMP_PK" and not "SYS_U012321".

In ERwin, right-click on the table and go to "ORACLE Index". Then make sure that there is a name for the primary key, and that the type is "PK".

Now, if you are generating scripts, ("Tasks", "Forward Engineer"), go to "Referential Integrity" and you can choose "Create/PK" or "Alter/PK". I use "Alter/PK" which first creates the index, and then alters the table defining the PK constraint.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 380+ Oracle tips, visit:                         <->
<->                                                      <->
<->             www.arikaplan.com                        <->
<->                                                      <->
<->             email: akaplan_at_interaccess.com           <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

On Thu, 17 Aug 2000, Carmen Rusu wrote:

> 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 ) ;
>
> Thanks,
>
> Carmen Rusu
> Oracle DBA
> --
> Author: Carmen Rusu
> INET: crusu_at_clearcommerce.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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
Received on Thu Aug 17 2000 - 12:20:30 CDT