chap6_5.html

来自「Oracle资料大集合」· HTML 代码 · 共 393 行 · 第 1/2 页

<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 &amp; 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 &amp; 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">6.5	How do I匘etermine foreign key dependencies?</font></b>

<P>

<B>Problem</B>

<p>

I want to list information about the constraints in the database. I need to determine the constraint name, the type of constraint, and the status of the constraint. I also need to find out the table and columns on which the constraint is defined. I know that all this information can be obtained from data dictionary views. How do I list information about constraints to determine foreign key dependencies in the database?

<p>

<B>Technique</B>

<p>

Constraint information can be queried from the USER_CONSTRAINTS or ALL_CONSTRAINTS data dictionary views. Columns associated with constraints can be viewed from the USER_CONS_COLUMNS or ALL_CONS_COLUMNS views. When the primary key is created, a unique index is placed on the table that can be listed from the USER_INDEXES view. Table 6.1 shows the columns in the USER_CONSTRAINTS view and details on how to interpret the value of the CONSTRAINT_TYPE column to find out the type of constraint definition.

<p>

Table 6.1  Columns in the USER_CONSTRAINTS view

<p>
<table border=0 cellpadding=3 cellspacing=3 bgcolor="cdcdcd">
<tr>
<td>Column</td>
<td>Description</td>
</TR>

<TR><td>OWNER</td>
<td>Owner of the constraint</td></TR>

<TR><td>CONSTRAINT_NAME</td>
<td>Name of the constraint</td></TR>

<TR><td>CONSTRAINT_TYPE</td>
<td>Type of constraint definition</td></TR>

<TR><td>TABLE_NAME</td>
<td>Name of the table associated with the constraint</td></TR>

<TR><td>SEARCH_CONDITION</td>
<td>Search condition used for CHECK constraints</td></TR>

<TR><td>R_OWNER</td>
<td>Owner of the table referenced by a FOREIGN KEY constraint</td></TR>

<TR><td>R_CONSTRAINT_NAME</td>
<td>Name of the constraint referenced by a FOREIGN KEY constraint</td></TR>

<TR><td>DELETE_RULE</td>
<td>Delete rule for a referential constraint: CASCADE or NO ACTION</td></TR>

<TR><td>STATUS</td>
<td>Status of constraint: ENFORCED, ENABLED, or DISABLED</td></TR>

<TR><td>DEFERRABLE (Oracle8)</td>
<td>Indicates whether the constraint is deferrable</td></TR>

<TR><TR><td>DEFERRED (Oracle8)</td>
<td>Indicates whether the constraint was initially deferred</td></TR></TR>
<TR>
<td>GENERATED (Oracle8)</td>
<td>Indicates whether the constraint name is system generated</td> </TR>

<TR><td>LAST_CHANGE (Oracle8)</TD>
<td>Indicates when the constraint was last enabled or disabled</td> </TR>

<TR><TD>VALIDATED (Oracle8)</TD>
<td>Indicates whether all data obeys the constraint: VALIDATED or NOT VALIDATED</td></TR>
</table>
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. Run the CHP6_14.SQL file. The USER_CONSTRAINTS view is queried to display information about constraints created in this chapter.
<p>
<b><code>
SQL> START CHP6_14
<P>
SQL> SET ECHO ON
<P>
SQL> COLUMN CONSTRAINT_NAME FORMAT A15
<P>
SQL> COLUMN TABLE_NAME FORMAT A10
<P>
SQL> COLUMN R_CONSTRAINT_NAME FORMAT A17
<P>
SQL> COLUMN SEARCH_CONDITION FORMAT A30
<P>
SQL> SELECT
<P>
2    CONSTRAINT_NAME,
<P>
3    TABLE_NAME,
<P>
4    CONSTRAINT_TYPE,
<P>
5    R_CONSTRAINT_NAME,
<P>
6    SEARCH_CONDITION,
<P>
7    STATUS
<P>
8  FROM
<P>
9    USER_CONSTRAINTS
<P>
10  ORDER BY 1, 2;
</b></code>
<P>
<A HREF="image/06oht14.gif" TARGET="_blank">Figure 6.14</A> shows the results of the query. The COLUMN commands at the top format column lengths, before running the statement. The columns returned by the query show you the name of the constraint, the type of constraint, and the name of the referenced key梚f it is a foreign key constraint. The CONSTRAINT_NAME column contains the unique name of the constraint. Use Table 6.2 to recognize the type of constraint from the character displayed under the CONSTRAINT_TYPE column. The query displays PRIMARY KEY constraints on tables created in previous sections. NOT NULL constraints show up as CHECK constraints with a value of 揅