📄 chap5_6.html
字号:
<HTML><HEAD><TITLE>Oracle8 How-To</TITLE><SCRIPT><!--function displayWindow(url, width, height) { var Win = window.open(url,"displayWindow",'width=' + width +',height=' + height + ',resizable=1,scrollbars=yes');}//--></SCRIPT></HEAD><BODY BACKGROUND="../../../images/curve_itlibrary_white.gif" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#DD0000" VLINK="#999999" ALINK="#FF0000"><!--Begin Header Table --><TABLE width="640" cellpadding="0" cellspacing="0" border="0"><TR><TD VALIGN="TOP" WIDTH="640" COLSPAN="3"><A HREF="http://www.earthweb.com" target="resource window"><IMG SRC="../../../images/eweb_banner.gif" VSPACE="6" WIDTH="640" HEIGHT="25" ALT="Brought to you by EarthWeb" border="0"><BR></a></TD></TR><TR><TD VALIGN="TOP" WIDTH="150"><A HREF="http://www.itlibrary.com/"><IMG SRC="../../../images/small_logo.gif" WIDTH=103 HEIGHT=82 ALT="IT Library Logo" border="0"></a><P><!--Begin Subscribe Box Table --><TABLE border="0" cellpadding="0" cellspacing="0" WIDTH="100"><TR><TD></TD></TR></TABLE><!--End Subscribe Box Table --></td><TD><img src="../../../images/dotclear.gif" WIDTH="15" HEIGHT="1"></TD><TD><!-- Begin Ads itl //--><a href="http://www.itlibrary.com/adclick.html/CID=00000349829b438e00000000/site=itlibrary/area=itl/aamsz=468x60" TARGET="new win"><img src="../../../../adimages.earthweb.com/images/ads/spidercatcher.gif@9614" ALT="Click Here!" border="0"><BR><CENTER>Click Here!</CENTER></a><!-- itl End Ads //--></td></TR></TABLE><!--End Header Table --><!--Begin Main Table --><TABLE width="640" cellpadding="0" cellspacing="0" border="0"><TR><!--Begin Left Navigation column --><TD VALIGN="TOP" WIDTH="140"><FORM action="/subscribe/list.cgi" method="post"><INPUT name='email' value='your e-mail' size="11"><INPUT type='hidden' name='state' value='Subscribe'><br><INPUT type='image' value='subscribe' img src='/images/subscribe.gif' border='0' vspace='3' ></center></form><form name="Search" method="GET" action="http://search.earthweb.com/search97/search_redir.cgi"><INPUT TYPE="hidden" NAME="Action" VALUE="Search"><INPUT TYPE="hidden" NAME="SearchPage" VALUE="http://search.earthweb.com/search97/samples/forms/srchdemo.htm"><INPUT TYPE="hidden" NAME="Collection" VALUE="Site Index"><INPUT TYPE="hidden" NAME="ResultTemplate" VALUE="itl.hts"><INPUT TYPE="hidden" NAME="ViewTemplate" VALUE="view.hts"></font><table width=130 border=0 cellpadding=0 cellspacing=0><tr><td><font face="arial, helvetica" size=2 color="white"><b>Search the site:</b></font><br><INPUT NAME="queryText" size=8 VALUE=""> <input type="submit" name="submitbutton" value="Go!"></td></tr><INPUT type=hidden NAME="section_on" VALUE="on"><tr><td><SELECT NAME="section" size=1><option value="../../../../www.itlibrary.com/default.htm" SELECTED>ITLibrary<option value="../../../../www.itknowledge.com/default.htm">ITKnowledge</SELECT><a href="http://www.itlibrary.com/search/"><font face="arial, helvetica" color=FFFFFF size=1>EXPERT SEARCH</a></font><IMG src="../../../images/rule.gif" width="100" vspace=3 height="2" ALT="-----"></form></td></tr></table><table bgcolor="#cc9900" cellpadding="0" cellspacing="0" border="0" width=110><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><b><a href="http://www.itlibrary.com/reference/dir.programminglanguages.html"><font face="verdana,helvetica" size="1" color="000000">Programming Languages</font></a></b></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.databases.html"><font face="verdana,helvetica" size="1" color="000000"><b>Databases</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.security1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Security</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.webservices.html"><font face="verdana,helvetica" size="1" color="000000"><b>Web Services</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.networkservices1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Network Services</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.middleware.html"><font face="verdana,helvetica" size="1" color="000000"><b>Middleware</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware & Collaboration</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun & Games</b></font></td></tr></table><!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0" ISMAP USEMAP="#leftnav"> --><p><a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a><a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a><a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a><a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a><a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a><P><b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br> <FONT SIZE="1"> <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br> <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br> <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br> <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br> <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br> <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br> <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br> <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br> <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br> <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br> <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br> <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br> <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br> <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br> <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br> <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br> <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br> <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br> <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a></FONT></td><!--End Left Navigation column --><!-- spacer column --><TD width="40"><img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0></TD><!--Begin Content Column --><TD VALIGN="TOP" width="500"><P><blockquote><b><font size="+1">5.6 How do I匛nforce uniqueness via an index?</font></b><P><B>Problem</B><p>My business rules require that each record in a table have unique values in certain columns. I want to enforce this rule by making a unique index. I know that this will also improve performance when querying data. How do I enforce uniqueness via an index?<p><B>Technique</B><p>Use the UNIQUE clause of the CREATE INDEX command to create unique indexes. A unique index will ensure that no two records will have the same values in the columns that comprise the index. Any attempt to add a new record that contains duplicate values will be rejected. Also, any attempt to alter the data contained in the indexed columns will be rejected.<p><B>Steps</B><p>1. Connect to SQL*Plus as the WAITE user account. If you have not created the EMPLOYEE05 table, run the CHP5_1.SQL script to create it. The description of the EMPLOYEE05 table appears in <A HREF="image/05oht13.gif" TARGET="_blank">Figure 5.13</A>.<p>The decision of which columns to make unique, if any, will depend on the business rules of your application. For example, it may not make sense to make the STATE column unique, as there may be more than one employee from a given state. It may also not make sense to make the EMPLOYEE_NAME column unique, as there may be two employees with the same name. Also, there may be two different possible employee names for a Social Security number (if you store nicknames, for example). In this How-To, we will create a unique index on the combination of EMPLOYEE_NAME and SSN columns for the EMPLOYEE05 table.<p>2. Create the unique index on the EMPLOYEE_NAME and SSN columns by running CHP5_11.SQL.<p>SQL> GET CHP5_11.sql<p>1 CREATE UNIQUE INDEX name_ssn_index<p>2 ON EMPLOYEE05 (EMPLOYEE_NAME, SSN)<p>SQL> /<p>Index created.<p>3. At this point, Oracle will automatically reject any records that duplicate the combination of EMPLOYEE_NAME and SSN. Insert a sample record into the EMPLOYEE05 table by running CHP5_12.SQL.<p>SQL> GET CHP5_12.sql<p>1 INSERT INTO EMPLOYEE05<p>2 VALUES (慉ARON LIPMAN⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -