ch12.htm

好书《C++ Builder高级编程技术》

<TT>CustNo</TT>:
<BR>
<BR>

<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="88" ALIGN="LEFT"><B>Tablename</B></TD>
		<TD WIDTH="124" ALIGN="LEFT"><B>Primary key</B></TD>
		<TD ALIGN="LEFT"><B>Foreign key (secondary index)</B></TD>
	
</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="88" ALIGN="LEFT"><TT>Customer</TT></TD>
		<TD WIDTH="124" ALIGN="LEFT"><TT>CustNo</TT></TD>
		<TD ALIGN="LEFT"></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="88" 
ALIGN="LEFT"><TT>Orders</TT></TD>
		<TD WIDTH="124" ALIGN="LEFT"><TT>OrderNo</TT></TD>
		<TD ALIGN="LEFT"><TT>CustNo</TT></TD>
	</TR>
</TABLE>
<BR>
Given this scenario, you can say &quot;Show me the set of all orders such that their
<TT>CustNo</TT> 
field is equal to X or within the range of X - Y.&quot; Computers
love these kinds of simple mathematical relationships. It's their bread and butter.
In essence, you are just asking for the intersection of two sets: &quot;Show me the
intersection of 
this record from the <TT>Customer</TT> table with all the records
from the <TT>Orders</TT> table.&quot; This intersection will consist of one record
from the <TT>Customer</TT> table with a particular <TT>CustNo</TT> plus all the records
from the 
<TT>Orders</TT> table that have the same <TT>CustNo</TT> in their foreign
key.</P>
<P>These <TT>CustNo</TT>, <TT>OrderNo</TT>, <TT>AuthorNo</TT>, <TT>BookNo</TT>, and
similar fields might also be used in flat-file databases as indexes, but they play
a 
unique role in relational databases because they are the keys used to relate different
tables. They make it possible to reduce the relationship between tables to nothing
more than a simple series of mathematical formulas. These formulas are based on 
keys
rather than on indexes. It is merely a coincidence that most keys also happen to
be indexed.
<H4><A NAME="Heading8"></A><FONT COLOR="#000077">Viewing Indices and Keys in DBD
or the Explorer</FONT></H4>
<P>In the next few sections I define primary 
and secondary keys, and describe how
to use them. It might be helpful if I preface this discussion with a brief description
of how to view keys using some of the tools that ship with BCB. This is just a preliminary
look at this material. I cover it 
again in greater depth later in this chapter in
a section called &quot;Exploring the Indices in the <TT>BCDEMOS</TT> Database.&quot;

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Right now it is not 
so important
	that you understand what primary and foreign keys do, but only that you know how
	to view them using the tools that ship with the product. The theory will become clear
	as the chapter progresses. 
<HR>


</BLOCKQUOTE>

<P>There are two 
ways to view the indexes and keys on a table. The best way is in
the Database Explorer. Open up the Explorer and view the <TT>BCDEMOS</TT> database
as shown in Figure 12.1.</P>
<P>Click the <TT>Orders</TT> table and open up the Referential Constraints 
branch
as shown in Figure 12.2. Notice that there are two constraints on this table, one
called <TT>RefCustInOrders</TT> and the second called <TT>RefOrders</TT>. The <TT>RefCustInOrders</TT>
field defined <TT>CustNo</TT> as a foreign key that relates 
to the <TT>CustNo</TT>
field in the <TT>Customer</TT> table.</P>
<P>A second way to view this key is in the Database Desktop. Set the Working Directory
from the File menu to <TT>BCDEMOS</TT>. Open up the <TT>Orders</TT> table in the
Database Desktop 
and select Table | Info structure from the menu. Drop down the Table
Properties and select Referential Integrity, as shown in Figure 12.3.<BR>
<BR>
<A NAME="Heading10"></A><A HREF="12ebu01.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu01.jpg">FIGURE 12.1.</A><FONT COLOR="#000077">

</FONT><I>Viewing the <TT>BCDEMOS</TT> database in the Database Explorer.</I>
<H6></H6>
<P><A NAME="Heading11"></A><A HREF="12ebu02.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu02.jpg">FIGURE 12.2.</A><FONT COLOR="#000077">
</FONT><I>The primary and foreign fields of the <TT>Orders</TT> 
table.</I>
<H6></H6>
<P><A NAME="Heading12"></A><A HREF="12ebu03.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu03.jpg">FIGURE 12.3.</A><FONT COLOR="#000077">
</FONT><I>Selecting Referential Integrity in the Database Desktop.</I></P>
<P>Double-click <TT>RefCustInOrders</TT> to bring up the 
Referential Integrity dialog
shown in Figure 12.4.
<H6></H6>
<P><A NAME="Heading13"></A><A HREF="12ebu04.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu04.jpg">FIGURE 12.4.</A><FONT COLOR="#000077">
</FONT><I>The <TT>CustNo</TT> field in the <TT>Orders</TT> table relates to the <TT>CustNo</TT>

field in the <TT>Customer</TT> table.</I></P>
<P>The fields in the left side of this dialog belong to the <TT>Orders</TT> table.
On the right is a list of all the tables in the database. In the center, you can
see that the <TT>CustNo</TT> field has 
been selected from the <TT>Orders</TT> table
and the <TT>CustNo</TT> field has been selected from the <TT>Customer</TT> table.
The primary key of the <TT>Customer</TT> table is related to the foreign key of the
<TT>Orders</TT> table.</P>
<P>Now go 
back to the Database Explorer and open up the Indices branch of the <TT>Orders</TT>
table, as shown in Figure 12.5.</P>
<P>Note that you can see the names of the indexes, here labeled as <TT>&lt;primary&gt;</TT>
and as <TT>CustNo</TT>. The fields 
found in the indexes are also displayed. For instance,
you can see that the primary index consists of the <TT>OrderNo</TT> field and the
secondary index consists of the <TT>CustNo</TT> field.<BR>
<BR>
<A NAME="Heading14"></A><A 
HREF="12ebu05.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu05.jpg">FIGURE 12.5.</A><FONT COLOR="#000077">
</FONT><I>The primary and <TT>CustNo</TT> indexes on the <TT>Orders</TT> table.</I></P>
<P>I am showing these to you so that you will begin to see the distinction between
keys and 
indexes. The two concepts are distinct. For further proof of this, open
up the <TT>IBLOCAL</TT> database in the Database Explorer. Use <TT>SYSDBA</TT> as
the user name, and <TT>masterkey</TT> as the password. Now open up the Employee project
table as 
shown in Figure 12.6. Note that there are separate listings for the index,
primary key, and foreign keys.<BR>
<BR>
<A NAME="Heading15"></A><A HREF="12ebu06.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu06.jpg">FIGURE 12.6.</A><FONT COLOR="#000077">
</FONT><I>The <TT>Employee_Project</TT> 
table has three indexes, one primary key,
and two foreign keys.</I></P>
<P>In practice, almost all keyed fields will also have indexes. This leads people
to think the two concepts are the same. However, indexes are about searching and
sorting, and 
keys are about referential integrity. These distinctions will become
blurred at times, but it helps if you can keep it in your mind that they are different
ideas. The actual details concerning these distinctions will become clear in the
next few 
pages.</P>
<P>You can also see the indexes for a table inside the Database Desktop. To get started,
open up the <TT>Orders</TT> table and select Table | Info Structure from the menu.
The fields with the stars beside them are part of the primary index. 
Drop down the
Table Properties combo box to view the secondary indexes. Double-click the indexes
you see to view the details of their design. If you want to change the structure
of a table, choose Table | Restructure from the menu, rather than Table | 
Info Structure.</P>
<P>Most of the time, I find the Database Desktop is the right tool to use when I
want to create or modify a table, and the Database Explorer is the right tool to
use when I want to view the structure of a table. However, I often 
find myself jumping
back and forth between the two tools, to get the best features of each. Later in
the book I will talk about case tools, which are generally superior to either of
the products discussed in this section. However, there are no case 
tools that ship
with BCB, so I emphasize the universally available tools in this text.</P>
<P>Throughout the ensuing discussion, you might have occasion to use the Database
Explorer to examine the structure of the <TT>Customer</TT>, <TT>Orders</TT>, 
<TT>Items</TT>,
and <TT>Parts</TT> tables. These are the tables I use when defining what relational
databases are all about.
<H3><A NAME="Heading16"></A><FONT COLOR="#000077">Rule Numero Uno: Create a Primary
Key for Each Table!</FONT></H3>
<P>The 
last two sections have introduced you to some of the key concepts in relational
databases. If there is one lesson to take out of this chapter, it is the importance
of creating a unique numerical key in the first field of most tables you create.
This 
field is called a primary key. In both Paradox and InterBase, it is impossible
to create a primary key without also simultaneously creating an index.</P>
<P>If you want to have a list of addresses in a table, don't just list the <TT>Address</TT>,

<TT>City</TT>, <TT>State</TT>, and <TT>Zip</TT>. Be sure to also include an integer-based
<TT>CustNo</TT>, <TT>AddressNo</TT>, or <TT>Code</TT> field. This field will usually
be both an index and the first field of the database. It is the primary key 
for your
table, and must be, by definition, unique. That is, each record should have a unique
<TT>Code</TT> field associated with it.</P>
<P>The primary key

<UL>
	<LI>Serves as the means of differentiating one record from another
	<P>
	<LI>Is also in 
referential integrity
	<P>
	<LI>Can also help with fast searches and sorts
</UL>

<P>As I said earlier, the distinction between indexes and keys becomes blurred at
times. However, they are distinct concepts and you should endeavor to discover the

differences.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In this discussion I am taking a
	liberty in saying that you have to create a primary key for a table in a relational
	database. In fact, 
you can simply create a field that contains a unique integer value.
	It doesn't have to be an index. However, making a unique index for the field will
	speed up the operation of your database, and it will help enforce rules that make
	it easy to 
create robust relational databases. In particular, the restraints on a
	primary key make it impossible for you to create two fields in one table with the
	same primary key. 
<HR>


</BLOCKQUOTE>

<P>Just to make sure this is clear, I'll go ahead and 
list out the right and wrong
way to create a table.


<BLOCKQUOTE>
	<P><I>Right Method</I><BR>
	<TT><BR>
	CustNo</TT>: Integer<BR>
	<TT>LastName</TT>, <TT>FirstName</TT>, <TT>Address</TT>, <TT>City</TT>, <TT>State</TT>,
	<TT>Zip</TT>: string</P>
	
<P><I>Wrong Method</I></P>
	<P><TT>LastName</TT>, <TT>FirstName</TT>, <TT>Address</TT>, <TT>City</TT>, <TT>State</TT>,
	<TT>Zip</TT>: string

</BLOCKQUOTE>

<P>The first example is &quot;correct&quot; because it has a primary index called
<TT>CustNo. 
</TT>It is declared as a unique <TT>Integer</TT> value. The second example
is &quot;wrong&quot; because it omits a simple numerical field as the primary index.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE:</B></FONT><B> </B>I put the words &quot;correct&quot;
	and &quot;wrong&quot; in quotes because there really are no hard-and-fast rules in
	this discipline. There are occasions when you might not want to create a table that
	
has a simple integer as a primary index. However, ninety-nine percent of the time,
	that's exactly what you want to do. <BR>
	<BR>
	At the height of a warm May spring day, there is such a thing as a rose bush that
	has no buds or flowers. However, the 
whole point of rose bushes in May is that they
	flower. I doubt we would feel quite the same way about roses if they did not have
	beautiful blooms. In the same way, relational databases without primary indexes wouldn't
	garner quite so much attention 
as they do now. <BR>
	<BR>
	I should add that not all primary indexes are numeric fields. For instance, many
	tables might use alpha fields containing values such as <TT>HDA1320WW35180</TT>.
	I'm stressing simple numeric fields in this chapter because 
they are easy to work
	with and easy to understand. 
<HR>


</BLOCKQUOTE>