Re: Preferred method in creating primary key

From: Jack Wang <nospam_at_nospam.com>
Date: Wed, 13 Aug 2003 18:12:42 GMT
Message-ID: <ucv_a.4545$zE1.1772@edtnps84>

A primary key is NOT necessary to have a unique index. Consider,

CREATE TABLE T1... SQL> ALTER TABLE T1
 ADD CONSTRAINT T1_PK
 PRIMARY KEY (Y)
 DEFERRABLE
 INITIALLY IMMEDIATE USING INDEX
 TABLESPACE INDX; SQL> SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME = 'T1_PK'; UNIQUENES INDEX_NAME

---------             ------------------------------

NONUNIQUE T1_PK DROP TABLE...
CREATE TABLE... SQL> ALTER TABLE T1
 ADD CONSTRAINT T1_PK
 PRIMARY KEY (Y)
 USING INDEX
 TABLESPACE INDX; SQL> SELECT INDEX_NAME, UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME = 'T1_PK'; UNIQUENES INDEX_NAME

---------             ------------------------------
UNIQUE            T1_PK

So the conclusion is if the constraint is enabled and there is no index that uses the constraint columns as a leading part of the index, an index with the same name as the constraint is created using the following rules: - If the key is deferrable, a nonunique index on the key column is created. - If the key is nondeferrable, a unique index is created.

Jack

>
> No difference exists, subtle or otherwise. A primary key MUST have a
unique
> index.
>
> My preference is to NEVER create an index. In fact I can't think of any
good
> reason to ever do so.
>
> CREATE TABLE ...
>
> ALTER TABLE <table_name>
> ADD CONSTRAINT pk_ ...
> PRIMARY KEY (<column_name_list>)
> USING INDEX
> TABLESPACE ...
>
> of course, as Norman points out, ... with DEFERRABLE and INITIALLY
IMMEDIATE.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>
Received on Wed Aug 13 2003 - 13:12:42 CDT