RE: Primary keys vs. unique indexes

And don't forget that with Oracle 8 you can create a unique/primary key that uses a non-unique index. The only requirement is that the key columns are leading columns in the index.

Kevin

$ sqlplus ktoepke

SQL*Plus: Release 8.0.5.0.0 - Production on Thu Nov 30 13:49:8 2000

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production With the Partitioning option
PL/SQL Release 8.0.5.1.0 - Production

SQL> CREATE TABLE my_test (

  2      col1    NUMBER(3) NOT NULL
  3     ,col2    NUMBER(3) NOT NULL
  4     ,col3    NUMBER(3));

Table created.

SQL> CREATE INDEX my_index ON my_test (
  2 col1, col2
  3 );

Index created.

SQL> alter table my_test add constraint my_pk primary key (col1);

Table altered.

SQL>
> -----Original Message-----
> From: bunyamin karadeniz [mailto:bunyamink_at_havelsan.com.tr]
> Sent: Thursday, November 30, 2000 12:41 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Primary keys vs. unique indexes
>
>
> THINK LIKE THAT :
>
> if your aim is to create only one table ( no relation with
> other tables) ,
> AND You make a field Primary key ,
> Then this means
> 1) you do not want nulls to enter to that field
> 2) you want your non null datas to be unique.(already there
> are no nulls
> :)) )
> 3) you want your datas to be indexed .
>
> if your aim is to create only one table ( no relation with
> other tables) ,
> AND You make a field unique key ,
> Then this means
> 1) null entering to table is not important for you.
> 2) you want your non-null data to be unique .
> 3) you want your all data ( nulls+nonnulls) to be indexed
>
> If your aim is to create a table with a column which is
> referenced by other
> tables with foreign keys , AND you make your field Primary Key
> Then this means you are so good becouse
> 1) You manage the relation
> 2) you do not want nulls to enter to that field
> 3) you want your non null datas to be unique.(already there
> are no nulls
> :)) )
> 4) you want your datas to be indexed .
>
> If your aim is to create a table with a column which is
> referenced by other
> tables with foreign keys , AND you make your field Unique
> Key AND your
> manager saw this.
> Then this means YOU WILL BE DISMISSED EVEN BEFORE THE TIME
> YOU MAY IMAGINE
> .
>
> **********************************************
> Bunyamin K. Karadeniz
> Information Technologies Dept.
> Database Group Tel :90-312-2873565/1681
> HAVELSAN Ankara / TURKEY
> **********************************************
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Thursday, November 30, 2000 7:50 PM
>
>
> > The main difference is you can not reference an unique
> > index with a foreign key.
> > --- cemail_at_sprintmail.com wrote:
> > >
> > > What is the difference between building a table and
> > > including
> > > the primary key versus building the table and then
> > > having creating
> > > a unique index on the same keys? When you create a
> > > table with
> > > a primary key, doesn't Oracle store it as an index
> > > anyway?
> > >
> > > -----
> > > Sent using MailStart.com (
> > > http://MailStart.Com/welcome.html )
> > > The FREE way to access your mailbox via any web
> > > browser, anywhere!
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author:
> > > INET: cemail_at_sprintmail.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! Shopping - Thousands of Stores. Millions of Products.
> > http://shopping.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: james ellis
> > INET: jellis24_gso_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
> > (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: bunyamin karadeniz
> INET: bunyamink_at_havelsan.com.tr
>
> 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 Thu Nov 30 2000 - 12:44:54 CST