Re: Preferred method in creating primary key

Jack Wang wrote:

> 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)
> >
> >

Frightening. If this isn't a bug ... who-ever designed it should be shown the door.

What a great way to corrupt a table.

--
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)