Re: Preferred method in creating primary key

It makes some sense that this index is not unique since the constraint checking is deferred until you commit. Before the commit, you are allowed to insert all the duplicate key values you want, but you must remove them before you commit. If the index were UNIQUE, then the duplicate key values would not be allowed.

Personally, I've hated deferrable constraint checking since it was introduced. We put constraints in the database so that the business rules on the data are adhered to. This is especially important since we don't trust the application to validate those business rules for us. So why do we decide to throw out those business rules "temporarily"? It just seemed to me to be a short cut for doing things right the first time. Maybe it's just my opinion though...

Cheers,
Brian

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

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"