Re: Preferred method in creating primary key

Jack Wang wrote:

> What is the preferred method in creating primary key?
>
> The subtle difference is that index created in method 2 is unique while
> nonunique in method 1. If I disable pk in method 1, the nonunique index
> remains intact. But if I disable pk in method 2, the unique index gets
> hidden until I re-enable pk, and the index gets moved to default tablespace
> (users) rather than INDX which is specified in the first place.
>
> Appreciated your advice.
> 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)