ch12.htm

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

</FONT></PRE>
<P>This is the same information found in the Data Dictionary, and it should prove
sufficient under most circumstances.
<H4><A NAME="Heading29"></A><FONT 
COLOR="#000077">Using the Database Desktop to Create
Indexes</FONT></H4>
<P>To create a unique primary key in a Paradox table, open up the Database Desktop,
and create a table with the first field declared as an Integer or autoincrement value.
Place a 
star next to the first field, which tells Paradox to create a primary index
on it, as shown in Figure 12.10.<BR>
<BR>
<A NAME="Heading30"></A><A HREF="12ebu10.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu10.jpg">FIGURE 12.10.</A><FONT COLOR="#000077">
</FONT><I>Place asterisks next to the 
first field or fields of a table to designate
the primary index.</I></P>
<P>To create a secondary index, drop down the table properties list and choose Secondary
Indices. (See Figure 12.11.) Click the Define button. Select the fields from your
table 
that you want to be part of your index. Click OK. A simple dialog will then
pop up asking you to name the index. I usually give the index a name based on the
fields being indexed. For instance, if I want to create an index on the <TT>CustNo</TT>

field, I would call the index <TT>CustNo</TT>, <TT>CustNoIndex</TT>, or <TT>ByCustNo</TT>.
If I wanted to create one on a field called <TT>Name</TT>, I would call the index
<TT>Name</TT>, <TT>NameIndex,</TT> or <TT>ByName</TT>.<BR>
<BR>
<A 
NAME="Heading31"></A><A HREF="12ebu11.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu11.jpg">FIGURE 12.11.</A><FONT COLOR="#000077">
</FONT><I>Creating a secondary index in a Paradox table.</I>
<H4 ALIGN="CENTER"></H4>
<H4><A NAME="Heading32"></A><FONT COLOR="#000077">Using the Database 
Desktop to Create
Primary and Foreign Keys</FONT></H4>
<P>To create a primary or foreign key on a Paradox table you need to define something
called referential integrity. You cannot define referential integrity without first
defining primary keys on 
both tables involved. There also must be an index on the
foreign key, but this index will be created automatically for you when you create
the foreign key.</P>
<P>In InterBase, the situation is somewhat different. The act of creating primary
or 
foreign keys will automatically define indexes. As I said earlier, there are little
variations on the main themes of relational databases, depending on what kind of
database you use.</P>
<P>In the <TT>Data</TT> subdirectory from the CD that ships with 
this book you will
find two tables called <TT>MasterTable</TT> and <TT>DetailTable</TT>. Figure 12.10
shows how to use the Database desktop to create the <TT>MasterTable</TT>. These tables
look like this, with the <TT>MasterTable</TT> listed first and 
the <TT>DetailTable</TT>
listed second: 
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="99" ALIGN="LEFT"><B>Field name</B></TD>
		<TD WIDTH="64" ALIGN="LEFT"><B>Type</B></TD>
		<TD WIDTH="90" ALIGN="LEFT"><B>Size</B></TD>
		<TD 
ALIGN="LEFT"><B>Primary index?</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="99" ALIGN="LEFT"><TT>Code</TT></TD>
		<TD WIDTH="64" ALIGN="LEFT">+</TD>
		<TD WIDTH="90" ALIGN="LEFT"></TD>
		<TD ALIGN="LEFT">*</TD>
	</TR>
	<TR ALIGN="LEFT" 
rowspan="1">
		<TD WIDTH="99" ALIGN="LEFT"><TT>Name</TT></TD>
		<TD WIDTH="64" ALIGN="LEFT">A</TD>
		<TD WIDTH="90" ALIGN="LEFT">25</TD>
		<TD ALIGN="LEFT"></TD>
	</TR>
</TABLE>
<BR>

<TABLE BORDER="1">
	<TR ALIGN="LEFT">
		<TD WIDTH="106" 
ALIGN="LEFT"><B>Field name</B></TD>
		<TD WIDTH="64" ALIGN="LEFT"><B>Type</B></TD>
		<TD WIDTH="85" ALIGN="LEFT"><B>Size</B></TD>
		<TD ALIGN="LEFT"><B>Primary index?</B></TD>
	</TR>
	<TR ALIGN="LEFT">
		<TD WIDTH="106" ALIGN="LEFT"><TT>Code</TT></TD>
		
<TD WIDTH="64" ALIGN="LEFT">+</TD>
		<TD WIDTH="85" ALIGN="LEFT"></TD>
		<TD ALIGN="LEFT">*</TD>
	</TR>
	<TR ALIGN="LEFT">
		<TD WIDTH="106" ALIGN="LEFT"><TT>MasterCode</TT></TD>
		<TD WIDTH="64" ALIGN="LEFT">I</TD>
		<TD WIDTH="85" ALIGN="LEFT"></TD>
		
<TD ALIGN="LEFT"></TD>
	</TR>
	<TR ALIGN="LEFT">
		<TD WIDTH="106" ALIGN="LEFT"><TT>SubName</TT></TD>
		<TD WIDTH="64" ALIGN="LEFT">A</TD>
		<TD WIDTH="85" ALIGN="LEFT">25</TD>
		<TD ALIGN="LEFT"></TD>
	</TR>
</TABLE>
<BR>
<BR>
To create referential 
integrity between these two tables, you should open up the
<TT>DetailTable</TT> in the Database Desktop. Open the Table | Restructure menu item.
Select Referential Integrity from the Table Properties combo box. Click the Define
button, and set things 
up so they look like they do in Figure 12.12. Click the OK
button and give this relationship a name, such as <TT>RefMasterDetail</TT>.<BR>
<BR>
<A NAME="Heading33"></A><A HREF="12ebu12.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu12.jpg">FIGURE 12.12.</A><FONT COLOR="#000077">

</FONT><I>Defining referential integrity between the <TT>DetailTable</TT> and <TT>MasterTable</TT>.</I></P>
<P>When you are done, you will have created primary keys and foreign keys on the
<TT>MasterTable</TT> and <TT>DetailTable</TT>. The best way to 
see these keys is
in the Database Explorer. On my system I used the BDE Configuration Utility to create
an alias called <TT>CUnleashed</TT> that points at the <TT>Data</TT> subdirectory.
If you open this alias in the Database Explorer and go to 
<TT>MasterTable</TT>, you
can see the primary and foreign keys, which Paradox calls Primary and Foreign Fields.
<H3><A NAME="Heading34"></A><FONT COLOR="#000077">Why Use Referential Integrity?</FONT></H3>
<P>Referential integrity is one of the most 
valuable tools in a database programmer's
arsenal. In particular, referential integrity will help guide the user so that they
do not accidentally enter invalid data, or accidentally delete needed records.</P>
<P>To see referential integrity in action, 
use the Database Desktop to enter two
records in the <TT>MasterTable</TT>. The first should have the word <TT>Day</TT>
in the <TT>Name</TT> field and the second should have the word <TT>Month</TT> in
the <TT>Name</TT> field. You do not have to fill in 
the <TT>Code</TT> field, because
it is an autoincrement field (+) and will be updated automatically. 
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="94" ALIGN="LEFT"><B>Code</B></TD>
		<TD WIDTH="95" ALIGN="LEFT"><B>Name</B></TD>
	
</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="94" ALIGN="LEFT"><TT>1</TT></TD>
		<TD WIDTH="95" ALIGN="LEFT">Days</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="94" ALIGN="LEFT"><TT>2</TT></TD>
		<TD WIDTH="95" ALIGN="LEFT">Months</TD>
	
</TR>
</TABLE>
<BR>
<BR>
In the <TT>DetailTable</TT>, enter in a few names of days of the week or months of
the year in the <TT>SubName</TT> field. Give the <TT>MasterCode</TT> field a <TT>1</TT>
if you are entering a day, and <TT>2</TT> if you are 
entering a month. 
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><B>Code</B></TD>
		<TD WIDTH="94" ALIGN="LEFT"><B>MasterCode</B></TD>
		<TD WIDTH="86" ALIGN="LEFT"><B>SubName</B></TD>
	</TR>
	<TR ALIGN="LEFT" 
rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><TT>1</TT></TD>
		<TD WIDTH="94" ALIGN="LEFT"><TT>1</TT></TD>
		<TD WIDTH="86" ALIGN="LEFT"><TT>Monday</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><TT>2</TT></TD>
		<TD 
WIDTH="94" ALIGN="LEFT"><TT>1</TT></TD>
		<TD WIDTH="86" ALIGN="LEFT"><TT>Tuesday</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><TT>3</TT></TD>
		<TD WIDTH="94" ALIGN="LEFT"><TT>2</TT></TD>
		<TD WIDTH="86" 
ALIGN="LEFT"><TT>January</TT></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><TT>4</TT></TD>
		<TD WIDTH="94" ALIGN="LEFT"><TT>2</TT></TD>
		<TD WIDTH="86" ALIGN="LEFT"><TT>February</TT></TD>
	</TR>
	<TR ALIGN="LEFT" 
rowspan="1">
		<TD WIDTH="101" ALIGN="LEFT"><TT>5</TT></TD>
		<TD WIDTH="94" ALIGN="LEFT"><TT>2</TT></TD>
		<TD WIDTH="86" ALIGN="LEFT"><TT>March</TT></TD>
	</TR>
</TABLE>
<BR>
<BR>
With this data in the tables, you could define a one-to-many 
relationship such that
if you viewed the <TT>MasterTable</TT> record with <TT>Days</TT> in the <TT>Name</TT>
field you would see only the days in the <TT>DetailTable</TT>, and if you selected
<TT>Months</TT>, you would see only the month names from 
the <TT>DetailRecord</TT>.</P>
<P>Referential integrity will do two things to help make sure that these tables stay
in good shape. It will prevent you from deleting a record in the <TT>MasterTable</TT>
that has detail records associated with it in the 
<TT>DetailTable</TT>. For instance,
if you select the <TT>MasterTable</TT>, set the Database Desktop in Edit mode and
press Control+Delete, you will not be able to delete a record from the <TT>MasterTable</TT>.
Referential integrity will prevent you 
from entering a value in the <TT>MasterCode</TT>
field of the <TT>DetailTable</TT> that is not in the primary key of the <TT>MasterTable</TT>.
For instance, if you tried to enter the number <TT>3</TT> in the <TT>DetailTable</TT>'s
<TT>MasterCode</TT> 
field, you would get the error message <TT>&quot;Master field
missing&quot;</TT>. This is because there is no record in the <TT>MasterTable</TT>
with a <TT>Code</TT> field of <TT>3</TT>. Of course, if you added a record to the
<TT>MasterTable</TT> 
with a <TT>Code</TT> field that had <TT>3</TT> in it, the database
would let you enter the data.</P>
<P>Needless to say, these rules are also enforced inside BCB. In your own programs,
you might want to create exception handlers that would pop up 
messages that explained
to the user exactly what was wrong, and why they could not perform a particular operation.
Most users would not respond well to an exception that said no more than &quot;Master
field missing!&quot;</P>
<P>That is the end of my 
explanation of relational databases. In the last few pages
you have learned about primary keys, foreign keys, indexes, referential integrity,
and how all these pieces fit together to help you create robust applications. In
the next few pages I will 
step you through some simple examples that illustrate these
points.
<H3><A NAME="Heading35"></A><FONT COLOR="#000077">One-to-Many Relationships: The
Code</FONT></H3>
<P>Now that you know something about the data in the <TT>Customer</TT>, 
<TT>Orders</TT>,
<TT>Items</TT>, and <TT>Parts</TT> tables, it's time to link them together in a single
program called <TT>Relate</TT>. To get started, begin a new project and add a data
module to it. Place four <TT>TTable</TT> objects and four 
<TT>TDataSource</TT> objects
on the data module, wire each data source to a <TT>TTable</TT> object, and then wire
each of the <TT>TTable</TT> objects to one of the four tables mentioned earlier.
You can also rename the <TT>TTable</TT> and 
<TT>TDataSource</TT> objects so that
they correspond with their respective tables, as shown in Figure 12.13.<BR>
<BR>
<A NAME="Heading36"></A><A HREF="12ebu13.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/12/12ebu13.jpg">FIGURE 12.13.</A><FONT COLOR="#000077">
</FONT><I>The data module for the 
<TT>Relate</TT> project.</I></P>
<P>Drop four <TT>TDBGrid</TT> objects on the main form for the project. Use the File
| Include Unit Header menu option to link <TT>Form1</TT> to <TT>DataModule1</TT>.
Wire the grids to the datasources on the 
datamodule, making sure that each grid has
its <TT>DataSource</TT> property assigned to a unique object. For instance, link
the first grid to the <TT>Customer</TT> table, the second to the <TT>Orders</TT>
table, and so on.</P>
<P>Using the names 
visible in Figure 12.4, click the <TT>OrdersTable</TT> component
and set its <TT>MasterSource</TT> property equal to <TT>CustomerSource</TT>, that
is, set its <TT>MasterSource</TT> equal to the <TT>TDataSource</TT> object that is
linked to the 
<TT>TTable</TT> object that hosts the <TT>Customer</TT> table. Set
the <TT>ItemsTable</TT> <TT>MasterSource</TT> property equal to <TT>OrdersSource</TT>
and the <TT>PartsTable</TT> <TT>MasterSource</TT> equal to <TT>ItemsSource</TT>.</P>
<P>Click the 
<TT>OrdersTable</TT> <TT>MasterFields</TT> property and link up the
<TT>Orders</TT> and <TT>Items</TT> tables on the <TT>CustNo</TT> field, as described
in Chapter 9, &quot;Using <TT>TTable</TT> and <TT>TDataSet</TT>.&quot; In the same
way, hook up 
the <TT>TblItems</TT> to <TT>OrdersTable</TT> k&aacute;[infinity]the
<TT>OrderNo</TT> field, and <TT>PartsTable</TT> to <TT>ItemsTable</TT> on the <TT>PartNo</TT>
field. If you set all the tables to active and then run the program, the result should

look like what you see in Figure 12.14.</P>
<P>Spend a little time mucking about with this program. Notice, for instance, that
if you change the selected item in the <TT>Customer</TT> table, the contents of the
grids showing the <TT>Orders</TT>, 
<TT>Items</TT>, and <TT>Parts</TT> tables will
change. In particular, notice that the <TT>CustNo</TT> in all the items in the <TT>Orders</TT>
table is always equal to the <TT>CustNo</TT> in the currently selected item in