Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Primary keys vs. unique indexes

RE: Primary keys vs. unique indexes

From: Zhong Hong <zhong_at_cantametrix.com>
Date: Thu, 30 Nov 2000 12:02:01 -0800
Message-Id: <10696.123351@fatcity.com> 





This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.



------_=_NextPart_001_01C05B08.671FB0D0


Content-Type: text/plain;


        charset="iso-8859-1"


Content-Transfer-Encoding: quoted-printable



Lisa,


Please be aware that Index and Constraint are 2 different physical =
objects


in a database and have their different area of applications
- Index is defined for quick reference to the data(enforce the =
uniqueness by


a unique index is NOT the reason for defining the index)
- Constraint is defined to enforce the data integrity



HTH=20


Zhong


It's another beautiful day!




-----Original Message-----


From: Winnie_Liu_at_infonet.com [mailto:Winnie_Liu_at_infonet.com]
Sent: Thursday, November 30, 2000 10:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Primary keys vs. unique indexes






Lisa,



James is correct. Foreign key cannot reference to just an unique index. =
The


referenced column had to have the unique constraint or the primary key
constraint.



Remember, an unique constraint is different from the unique index. The
constraint will automatically create an unique index for you. But the =
index


will NOT automatically create a constraint for you.



Winnie







"Koivu, Lisa" <lkoivu_at_qode.com>@fatcity.com on 11/30/2000 09:20:21 AM



Please respond to ORACLE-L_at_fatcity.com



Sent by:  root_at_fatcity.com




To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:







What?=A0 Yes you can.=A0 I have anyway.



Can you give me an example of when that would not work?



Lisa Rutland Koivu


Oracle Database Administrator


Qode.com


4850 North State Road 7


Suite G104


Fort Lauderdale, FL=A0 33319


V: 954.484.3191, x174
F: 954.484.2933
C: 954.658.5849




http://www.qode.com



"The information contained herein does not express the opinion or =
position


of Qode.com and cannot be attributed to or made binding upon Qode.com."



-----Original Message-----


Sent: Thursday, November 30, 2000 11:51 AM
To: Multiple recipients of list ORACLE-L



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?=A0 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:

>=A0=A0 INET: cemail_at_sprintmail.com

>

> Fat City Network Services=A0=A0=A0 -- (858) 538-5051=A0 FAX:

> (858) 538-5051

> San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- 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).=A0 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
=A0 INET: jellis24_gso_at_yahoo.com

Fat City Network Services=A0=A0=A0 -- (858) 538-5051=A0 FAX: (858) =
538-5051
San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- 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).=A0 You may
also send the HELP command for other information (like subscribing).




--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--=20
Author:=20
  INET: Winnie_Liu_at_infonet.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).

------_=_NextPart_001_01C05B08.671FB0D0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2651.75">
<TITLE>RE: Primary keys vs. unique indexes</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Lisa,</FONT>
<BR><FONT SIZE=3D2>Please be aware that Index and Constraint are 2 =
different physical objects in a database and have their different area =
of applications</FONT></P>

<P><FONT SIZE=3D2>- Index is defined for quick reference to the =
data(enforce the uniqueness by a unique index is NOT the reason for =
defining the index)</FONT></P>

<P><FONT SIZE=3D2>- Constraint is defined to enforce the data =
integrity</FONT>
</P>

<P><FONT SIZE=3D2>HTH </FONT>
</P>

<P><FONT SIZE=3D2>Zhong</FONT>
<BR><FONT SIZE=3D2>It's another beautiful day!</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Winnie_Liu_at_infonet.com [<A =
HREF=3D"mailto:Winnie_Liu_at_infonet.com">mailto:Winnie_Liu_at_infonet.com</A>=
]</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, November 30, 2000 10:05 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: RE: Primary keys vs. unique indexes</FONT>
</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>Lisa,</FONT>
</P>

<P><FONT SIZE=3D2>James is correct. Foreign key cannot reference to =
just an unique index. The</FONT>
<BR><FONT SIZE=3D2>referenced column had to have the unique constraint =
or the primary key</FONT>
<BR><FONT SIZE=3D2>constraint.</FONT>
</P>

<P><FONT SIZE=3D2>Remember, an unique constraint is different from the =
unique index. The</FONT>
<BR><FONT SIZE=3D2>constraint will automatically create an unique index =
for you. But the index</FONT>
<BR><FONT SIZE=3D2>will NOT automatically create a constraint for =
you.</FONT>
</P>

<P><FONT SIZE=3D2>Winnie</FONT>
</P>
<BR>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>&quot;Koivu, Lisa&quot; =
&lt;lkoivu_at_qode.com&gt;@fatcity.com on 11/30/2000 09:20:21 AM</FONT>
</P>

<P><FONT SIZE=3D2>Please respond to ORACLE-L_at_fatcity.com</FONT>
</P>

<P><FONT SIZE=3D2>Sent by:&nbsp; root_at_fatcity.com</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>To:&nbsp;&nbsp; Multiple recipients of list ORACLE-L =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>cc:</FONT>
</P>
<BR>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>What?=A0 Yes you can.=A0 I have anyway.</FONT>
</P>

<P><FONT SIZE=3D2>Can you give me an example of when that would not =
work?</FONT>
</P>

<P><FONT SIZE=3D2>Lisa Rutland Koivu</FONT>
<BR><FONT SIZE=3D2>Oracle Database Administrator</FONT>
<BR><FONT SIZE=3D2>Qode.com</FONT>
<BR><FONT SIZE=3D2>4850 North State Road 7</FONT>
<BR><FONT SIZE=3D2>Suite G104</FONT>
<BR><FONT SIZE=3D2>Fort Lauderdale, FL=A0 33319</FONT>
</P>

<P><FONT SIZE=3D2>V: 954.484.3191, x174</FONT>
<BR><FONT SIZE=3D2>F: 954.484.2933</FONT>
<BR><FONT SIZE=3D2>C: 954.658.5849</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.qode.com" =
TARGET=3D"_blank">http://www.qode.com</A></FONT>
</P>

<P><FONT SIZE=3D2>&quot;The information contained herein does not =
express the opinion or position</FONT>
<BR><FONT SIZE=3D2>of Qode.com and cannot be attributed to or made =
binding upon Qode.com.&quot;</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, November 30, 2000 11:51 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
</P>

<P><FONT SIZE=3D2>The main difference is you can not reference an =
unique</FONT>
<BR><FONT SIZE=3D2>index with a foreign key.</FONT>
<BR><FONT SIZE=3D2>--- cemail_at_sprintmail.com wrote:</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; What is the difference between building a table =
and</FONT>
<BR><FONT SIZE=3D2>&gt; including</FONT>
<BR><FONT SIZE=3D2>&gt; the primary key versus building the table and =
then</FONT>
<BR><FONT SIZE=3D2>&gt; having creating</FONT>
<BR><FONT SIZE=3D2>&gt; a unique index on the same keys?=A0 When you =
create a</FONT>
<BR><FONT SIZE=3D2>&gt; table with</FONT>
<BR><FONT SIZE=3D2>&gt; a primary key, doesn't Oracle store it as an =
index</FONT>
<BR><FONT SIZE=3D2>&gt; anyway?</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; -----</FONT>
<BR><FONT SIZE=3D2>&gt; Sent using MailStart.com (</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://MailStart.Com/welcome.html" =
TARGET=3D"_blank">http://MailStart.Com/welcome.html</A> )</FONT>
<BR><FONT SIZE=3D2>&gt; The FREE way to access your mailbox via any =
web</FONT>
<BR><FONT SIZE=3D2>&gt; browser, anywhere!</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; --</FONT>
<BR><FONT SIZE=3D2>&gt; Please see the official ORACLE-L FAQ:</FONT>
<BR><FONT SIZE=3D2>&gt; <A HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>&gt; --</FONT>
<BR><FONT SIZE=3D2>&gt; Author:</FONT>
<BR><FONT SIZE=3D2>&gt;=A0=A0 INET: cemail_at_sprintmail.com</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT SIZE=3D2>&gt; Fat City Network Services=A0=A0=A0 -- (858) =
538-5051=A0 FAX:</FONT>
<BR><FONT SIZE=3D2>&gt; (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>&gt; San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- =
Public Internet</FONT>
<BR><FONT SIZE=3D2>&gt; access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2>&gt;</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>&gt; To REMOVE yourself from this mailing list, send =
an</FONT>
<BR><FONT SIZE=3D2>&gt; E-Mail message</FONT>
<BR><FONT SIZE=3D2>&gt; to: ListGuru_at_fatcity.com (note EXACT spelling =
of</FONT>
<BR><FONT SIZE=3D2>&gt; 'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>&gt; the message BODY, include a line containing: =
UNSUB</FONT>
<BR><FONT SIZE=3D2>&gt; ORACLE-L</FONT>
<BR><FONT SIZE=3D2>&gt; (or the name of mailing list you want to be =
removed</FONT>
<BR><FONT SIZE=3D2>&gt; from).=A0 You may</FONT>
<BR><FONT SIZE=3D2>&gt; also send the HELP command for other =
information</FONT>
<BR><FONT SIZE=3D2>&gt; (like subscribing).</FONT>
</P>

<P><FONT =
SIZE=3D2>__________________________________________________</FONT>
<BR><FONT SIZE=3D2>Do You Yahoo!?</FONT>
<BR><FONT SIZE=3D2>Yahoo! Shopping - Thousands of Stores. Millions of =
Products.</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://shopping.yahoo.com/" =
TARGET=3D"_blank">http://shopping.yahoo.com/</A></FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Author: james ellis</FONT>
<BR><FONT SIZE=3D2>=A0 INET: jellis24_gso_at_yahoo.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services=A0=A0=A0 -- (858) =
538-5051=A0 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, California=A0=A0=A0=A0=A0=A0=A0 -- Public =
Internet access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
from).=A0 You may</FONT>
<BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P>
<BR>
<BR>
<BR>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: </FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: Winnie_Liu_at_infonet.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =


Received on Thu Nov 30 2000 - 14:02:01 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US