This is how I create my primary key and unique indexes with the table create
statement. Pretty much does it all in one statement...
REATE TABLE PROJECT (
PROJECT_ID NUMBER (16) NOT NULL,
PROJECT_NUMBER VARCHAR2 (50) NOT NULL,
PIR_ID NUMBER (16) NOT NULL,
CUSTOMER_ID NUMBER (16) NOT NULL,
NOTE VARCHAR2 (2000),
STATUS VARCHAR2 (50) NOT NULL,
CONSTRAINT PROJECT_UK
UNIQUE (PROJECT_NUMBER)
USING INDEX
TABLESPACE TBSPC_INDEX01 PCTFREE 10
STORAGE ( INITIAL 1024K NEXT 1024K PCTINCREASE 0 ),
CONSTRAINT PROJECT_PK
PRIMARY KEY ( PROJECT_ID )
USING INDEX
TABLESPACE TBSPC_INDEX01 PCTFREE 10
STORAGE ( INITIAL 1024K NEXT 1024K PCTINCREASE 0 ))
TABLESPACE TBSPC_DATA01
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1048576
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 505
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Shakeel
> Qureshi
> Sent: Friday, January 12, 2001 2:21 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Primary Constraints and Indexes
>
>
> When you declare pk or uk constraint at the time of
> table creation the system automatically creates
> an index when the table is created. However, if you
> add with the constraint syntax like "using index
> tablespace", it means that the system generated index
> should be created on tablespace named INDEX, which is
> equally good like any other index for data search.
> However, if you want you can drop this index and
> create a new one with your own naming conventions and
> storage parameters.
>
> Hope this clarifies the process.
>
> Regards,
>
> Shakeel Qureshi
> DBA (Oracle Certified)
> squeshi_at_barpoint.com
>
>
> --- Kevin Kostyszyn <kevin_at_dulcian.com> wrote:
> > Why are you creating the PK like that? Create the
> > PK, then create the index
> > after the table is created.
> > Wouldn't that accomplish what you want?
> > Kev
> >
> > -----Original Message-----
> > McGill
> > Sent: Wednesday, January 10, 2001 10:46 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > As a part-time dba, I am confused about the
> > differences between
> > Primary Keys and Indexes.
> >
> > For example if I create a table "EMPLOYEES" and in
> > the table
> > creation statement I do something like:
> > create table ...
> > constraint XXX primary key (EMP_ID) using
> > index ...
> > then later I create an index:
> > create index YYY on EMPLOYEE (EMP_ID) ...
> > I know I have created two indexes (the first one
> > system-named
> > and implicitly created and the second name YYY and
> > created
> > explicitly) but do I really need the second index
> > YYY?
> > It seems to me the YYY index is not really needed?
> > Can
> > someone shed some light on this for me.
> >
> > THANKS, Wayne
> > McGillWL_at_navair.navy.mil
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Wayne McGill
> > INET: McGillWL_at_navair.navy.mil
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Kevin Kostyszyn
> > INET: kevin_at_dulcian.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Photos - Share your holiday photos online!
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shakeel Qureshi
> INET: msklq_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Fri Jan 12 2001 - 13:57:13 CST
