ch16.htm

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

=========== 
==================================================

          1 Fred

          2 Sam

          3 Joe

</FONT></PRE>
<P>If necessary, you can then repeat the process to copy <TT>AName</TT> to a field
called <TT>Name</TT>, or else you can just keep 
the new name for your table.</P>
<P>This whole technique is a bit laborious. However, if you play with SQL for a while,
all this work starts to become second nature. For instance, I can copy the <TT>AName</TT>
field back to a new field called 
<TT>name</TT> in well under a minute just by rapidly
typing the following:</P>
<PRE><FONT COLOR="#0066FF">alter table foo add Name varchar(50) not null;

update Foo

  set Name = AName;

alter table foo drop Aname

</FONT></PRE>
<P>Once you know SQL, 
and assuming you can type well, you can usually invoke WISQL,
enter the commands, and get out faster than you can load the weighty Database Desktop
application. Certainly by the time I open Database Desktop, open the Restructure
window, and start 
making changes, I've usually spent more time than it takes to do
the whole procedure in WISQL. This argument is a bit like the command line versus
the Windows Explorer. The Windows Explorer is considerably more intuitive to use,
but it is not 
necessarily faster than using the command line. Another nice thing
about WISQL is that it has a small footprint and can be left in memory without slowing
down the system.
<H3><FONT COLOR="#000077">Creating Blob Fields</FONT></H3>
<P>After the 
discussion of <TT>Code</TT> fields and the related <TT>Null</TT> versus
<TT>Not Null</TT> issues, the other fields in the Artist table are pretty straightforward:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE ARTIST (CODE CODE_DOM NOT NULL,

        
LAST VARCHAR(30),

        FIRST VARCHAR(30),

        BORN DATE,

        DIED DATE,

        BIRTHPLACE VARCHAR(35),

        COMMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 80,

        ARTISTTYPE INTEGER NOT NULL,

        PRIMARY KEY (CODE));


</FONT></PRE>
<P>The code for creating a blob field looks a bit tricky, but basically, you can
just repeat this code any time you need to create a text blob in InterBase. If you
create a blob field as shown previously, then you can use it with the 
<TT>TDBMemo</TT>
data-aware control that ships with BCB.</P>
<P>BCB offers two objects for working with blobs, <TT>TBlobField</TT> and <TT>TBlobStream</TT>.
<TT>TBlobField</TT> has methods called <TT>LoadFromFile</TT>, <TT>SaveToFile</TT>,

<TT>LoadFromStream</TT>, and <TT>SaveToStream</TT> that can be used to read and write
blob data in and out of a database. You can also usually cut and paste data directly
into a <TT>TDBMemo</TT> or <TT>TDBImage</TT> control by copying it to the 
clipboard
and then pasting it into the control using Ctrl+V. To copy an image from a blob field
to the clipboard, use Ctrl+C or Ctrl+X or else use the built-in <TT>CopyToClipBoard</TT>
feature of both <TT>TDBMemo</TT> or <TT>TDBImage</TT>.</P>
<P>You 
can use blob fields to store bitmapped images, sounds, video segments, and
text.
<H3><FONT COLOR="#000077">Primary Keys and Foreign Keys</FONT></H3>
<P>The final line in the definition for the Artist table defines the primary key:</P>
<PRE><FONT 
COLOR="#0066FF">PRIMARY KEY (CODE));

</FONT></PRE>
<P>This states that the primary key is the <TT>Code</TT> field. It's important that
<TT>Code</TT> is a keyed field because it is referenced by a foreign key in the Album
table. Furthermore, you want 
to be sure that no two rows have the same code in it,
and the primary key syntax enforces this rule. Remember that all primary keys must
be <TT>Not Null</TT> by definition.</P>
<P>Here, once again in slightly different form, is the definition for the 
Album table:</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE ALBUM (CODE CODE_DOM NOT NULL,

        ALBUM VARCHAR(25) NOT NULL,

        TYPES SMALLINT,

        LOUDNESS SMALLINT,

        MEDIUM SMALLINT,

        RATING SMALLINT,

        GROUPCODE 
CODE_DOM NOT NULL,

        PRIMARY KEY (CODE),

        FOREIGN KEY (TYPES) REFERENCES TYPES(CODE),

        FOREIGN KEY (LOUDNESS) REFERENCES LOUDNESS(CODE),

        FOREIGN KEY (MEDIUM) REFERENCES MEDIUM(CODE),

        FOREIGN KEY (GROUPCODE) 
REFERENCES ARTIST(CODE)

        );

</FONT></PRE>
<P>This time through, I modified the table to include foreign keys. These keys show
the dependencies that this table has on the fields of other tables.</P>
<P>Once again, the <TT>Code</TT> field is 
the primary key. This field contains a
unique number for each new Album record entered by the user. A character field designates
the name of the album or book, and the <TT>GroupCode</TT> field relates each record
to the Artist table.</P>
<P>Notice 
that the <TT>GroupCode</TT> field is a foreign key referencing the <TT>Code</TT>
field of the Artist table. A foreign key provides referential integrity. The foreign
key asserts that

<UL>
	<LI>Every <TT>GroupCode</TT> entry must have a corresponding 
<TT>Code</TT> field
	in the Artist table.
	<P>
	<LI>You can't delete an Artist record if there is a corresponding record in the Album
	table with a <TT>GroupCode</TT> the same as the <TT>Code</TT> field of the record
	you want to delete.
</UL>


<P>These two rules go a long way toward describing what foreign keys are all about.
They also help to explain what referential integrity is all about. In particular,
note that these rules are enforced by the server, and they are implemented regardless

of what type of front end attempts to alter the table.</P>
<P>You rarely want to make a foreign key unique because the whole point of this exercise
is to relate multiple albums with one artist. The artist is the master in the master-detail

relationship because there is only one artist for each set of albums. The artist
has a primary key, which means there is only one of each artist entered in the database.
The album is a foreign key, and there are usually multiple <TT>GroupCode</TT> 
foreign
keys for each single <TT>Code</TT> primary key.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>As you saw earlier, referential integrity
	is not unique to InterBase. In fact, Paradox supplies good tools for supporting 
referential
	integrity. It's built right into the Database Desktop, and every Paradox table that
	you create can have referential integrity if you want it. In most cases, you do indeed
	want it! 
<HR>


</BLOCKQUOTE>

<P>To see referential integrity 
in action, run the Music program that comes with
this book and try to delete one of the Artist records that has an Album associated
with it. For instance, try to delete Bob Dylan, Miles Davis, or Philip Glass. Your
efforts are stymied because there 
are albums associated with all these artists. In
particular, you get the lovely message that reads</P>
<PRE><FONT COLOR="#0066FF">General SQL Error: Violates FOREIGN KEY constraint &quot;INTEG_19&quot; on table &quot;Album&quot;

</FONT></PRE>
<P>You 
might as well savor this one because it is as close to poetry as you can get
in the SQL database world.</P>
<P>Go into the Database Desktop, enter a new album, and try to give it a <TT>GroupCode</TT>
that does not have a corresponding entry in the 
<TT>Code</TT> field of the Artist
table. The Database Desktop doesn't let you do it. (Note that there are other fields
that have foreign keys in this table, so you have to give valid values all the way
around, or you aren't able to enter a record. You 
can, however, leave the other fields
blank if you want.)</P>
<P>The key point here is that referential integrity is enforced automatically in
BCB and the Database Desktop. In fact, the rules are enforced on the server side,
so no matter how you try to 
get at the data, you must obey the rules. It's not just
some client-side code in BCB; the rule is built into the database itself, which is
what you want.</P>
<P>This concept is so important that I will repeat it once more: These rules are
enforced 
automatically no matter what front end the user attempts to use on the table!</P>
<P>The <TT>Types</TT>, <TT>Loudness</TT>, <TT>Medium</TT>, and <TT>Rating</TT> fields
are all integers. <TT>Types</TT>, <TT>Loudness</TT>, and <TT>Medium</TT> are all

foreign keys that reference one of three small tables called, logically enough, Types,
Loudness, and Medium:</P>
<PRE><FONT COLOR="#0066FF">/* Table: LOUDNESS, Owner: SYSDBA */

CREATE TABLE LOUDNESS (LOUDNESS VARCHAR(15) NOT NULL,

        CODE 
INTEGER NOT NULL,

        PRIMARY KEY (CODE));

/* Table: MEDIUM, Owner: SYSDBA */

CREATE TABLE MEDIUM (MEDIUM VARCHAR(15) NOT NULL,

        CODE INTEGER NOT NULL,

        PRIMARY KEY (CODE));

/* Table: TYPES, Owner: SYSDBA */

CREATE TABLE TYPES 
(TYPES VARCHAR(15) NOT NULL,

        CODE INTEGER NOT NULL,

        PRIMARY KEY (CODE));

</FONT></PRE>
<P>The structure of these tables ought to be intuitively obvious. The Types table,
for instance, is designed to hold the following records:</P>

<PRE><FONT COLOR="#0066FF">select * from types

TYPES                  CODE

=============== ===========

JAZZ                      1

ROCK                      2

CLASSICAL                 3

NEW AGE                   4

FOLK                      5


BLUES                     6

COMPUTER               1000

FICTION                1001

SCIFI                  1002

MYSTERY                1003

REFERENCE              1004

</FONT></PRE>
<P>What you have here are six types for albums and five types 
for books. I separate
the two types of types by a large range so that you can add a virtually unlimited
number of additional types of either kind. (If you want to work with more that 999
different types of music, you have a problem! Of course, I could 
have made the split
at 10,000 or 100,000 instead of at 1,000, but it's unlikely you want to have more
than 999 distinct types of music in this, or any other, database.)</P>
<P>The key point to grasp here is that you cannot add a number to the 
<TT>Types</TT>
field of the Album table unless it has a corresponding entry in the Types table.
The foreign key on the <TT>Types</TT> field is placed there explicitly to enforce
this rule. Furthermore, you can't delete an entry from the Types table if 
it has
a corresponding element in the <TT>Types</TT> field of the Album table. You can,
however, change the content of one of the strings in the Types table and thereby
either enhance, or totally trash, your data.</P>
<P>Astute readers probably notice 
that I designed the relationship between the <TT>Types</TT>
field of the Album table and the Types table itself so that it is easy to perform
lookups on the Types table when necessary. You will hear more about this topic later
in the chapter, or you 
can refer to the discussion of lookup fields in Chapter 11,
&quot;Working with Field Objects.&quot;</P>
<P>Here is the definition for the Book table, which plays the same role in this program
as the Album table. The key point to notice, however, is 
that the two tables differ
in several particulars. The interesting thing about the Music program is that it
can handle both kinds of tables seamlessly. To the user, the forms involved with
displaying this data just seem to morph as needed to 
accommodate the data.</P>
<PRE><FONT COLOR="#0066FF">CREATE TABLE BOOK (CODE CODE_DOM NOT NULL,

        ALBUM VARCHAR(25) NOT NULL,

        TYPES SMALLINT,

        MEDIUM SMALLINT,

        RATING SMALLINT,

        COMMENT BLOB SUB_TYPE TEXT 
SEGMENT SIZE 80,

        GROUPCODE CODE_DOM NOT NULL,

        PRIMARY KEY (CODE),

        Foreign key(GroupCode) references Artist(Code),