Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Weird pk index, please illuminate !
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