Chapter
13
Chapter 13
Creating
Databases with SQL
The earlier chapter on SQL (Day 8, "Selecting
Data with SQL") focused on SQL's Data Manipulation Language (DML) keywords. Today's
work focuses on SQL's Data Definition Language (DDL) keywords.
On Day 8, you learned how easy it is to select and
order data using the SQL SELECT_FROM clause. You also learned that using SQL
statements to perform data selection means that your Visual Basic programs work with
almost any back-end database server you might encounter in the future.
In today's lesson, you'll learn that you can use SQL
statements to create your databases, too. Using SQL keywords to create your data tables,
to set relationships, and to create indexes gives your programs an added level of
portability. The SQL words you learn today work not only on Microsoft Access-formatted
databases, but also on any database format that is SQL compliant. The skills you learn
today can be applied to almost every database engine on the market.
By the time you are through with today's lesson, you
will be able to use SQL keywords to perform the following tasks:
- Create and delete data tables with the CREATE
TABLE and DROP TABLE keywords.
- dd and delete fields in an existing data table using
the ADD COLUMN and DROP COLUMN keywords.
- Create and delete indexes using the CREATE INDEX
and DROP INDEX keywords.
- Define table relationships including foreign keys
using the PRIMARY KEY and FOREIGN KEY_REFERENCES keywords.
Throughout today's lesson, you'll use a program
called SQL-VB. This is a Visual Basic program that processes SQL scripts. All the
commands you learn today are in the form of SQL scripts. You can use the SQL-VB5 program
without knowing much about how it was built. However, if you are curious about how SQL-VB5
works, you can refer to Appendix A, "The SQL-VB Project." This appendix
walks you through a step-by-step construction of SQL-VB. It also contains
information on how to use SQL-VB5 with this guide and with other projects you
create in the future.
Using the
SQL-VB5 Interpreter
Before you begin today's lesson in advanced SQL
commands, you'll take a quick tour of the SQL-VB5 program. You'll learn how to
use SQL-VB5 to create, edit, and run SQL scripts. The SQL-VB5
Interpreter is a program that reads and executes SQL command scripts. You'll use this
program throughout the lesson today. You might also find this program useful in the future
for creating and managing SQL databases.
Loading and
Running the SQL-VB5 Interpreter
To load the SQL-VB5 Interpreter, locate the
TYSDBVB5\SOURCE\SQLVB5 directory that was created from the installation CD (for
installation information, refer to the last page of this guide). In the Windows Explorer or
in File Manager, double-click on the SQLVB5.EXE file (this is a 32-bit
application) to start the program. After the program loads, you should see a screen that
looks similar to the one in Figure 13.1.
The opening screen is actually a multidocument
interface. You can load and run one or more scripts from this interface. To test the
system, load and run a simple test script. Using SQL-VB5, select File | Run, and
at the Load SQLVB File dialog, locate and select SQLVB01.SQV (see Figure 13.2).
Figure 13.1. The opening
screen for the SQL-VB5 Interpreter.
Figure 13.2. Loading
the SQLVB01.SQV SQL script.
When you select the script, SQL-VB5 begins to read and process the SQL commands
in the file. This test script opens the guideS5.MDB database and then creates six
result sets and displays them on the screen. When the script is completed, you see a
dialog box announcing the completion of the script along with several result sets
displayed on the screen, as shown in Figure 13.3.
Figure 13.3. The completed
SQLVB01.SQV script.
SQL-VB5 creates all result set forms in a cascade starting at the top left of the
screen. You can change this to a tiled view by selecting Windows | Tile Horizontal from
the main menu (see Figure 13.4).
Figure 13.4. Tiling the
open forms.
You can also use your mouse pointer to resize, minimize, or maximize any form. You can
even resize individual columns and rows within a form. Figure 13.5 shows several of the
ways you can alter the view of forms.
Figure 13.5. Altering the
form views within SQL-VB5.
Creating and Editing SQL-VB5 Scripts
You can also use SQL-VB5 to create and edit
SQL command scripts. For example, edit the SQLVB01.SQV script you tested earlier.
First, load the script for editing by selecting File | Edit from the main menu. Locate and
select the SQLVB01.SQV script. When you select the script, SQL-VB5
launches the Notepad editor and loads the selected SQL script.
Let's change the SQL script so that the first result
set includes only authors whose AUID is less than 7. To do this, add the
text WHERE AUID<7; to the first SELECT statement. Be sure to place
the semicolon (;) at the end of the line. SQL-VB5 needs this character
to indicate the end of an SQL statement. Also, let's comment out the rest of the view sets
for now. You only want to see one result set in this test. To do this, add two slashes (//)
to the start of all the other lines that contain SELECT statements. Be sure to
place a space after the // comment sign. Your script should now resemble Listing
13.1.
Listing
13.1. Modifying a SQL-VB5 script.
//
// test sql command file for sqlvb interpreter
//
// open the database
dbOpen \tysdbvb5\source\data\guides5.mdb;
// open some tables to view
SELECT * FROM Authors WHERE AUID<7;
// SELECT * FROM Titles;
// SELECT * FROM Publishers;
// SELECT * FROM guideSales;
// SELECT * FROM Buyers;
// SELECT * FROM PublisherComments;
//
// eof
// |
After you have changed the script, save it using the File | Save command of Notepad. Now
select File | Run from the SQL-VB5 main menu to run the updated SQLVB01.SQV
command script. Your results should look similar to those in Figure 13.6.
Figure 13.6. The results
of the edited SQLVB01.SQV script.
You can create new SQL-VB5 scripts by selecting File | New from the menu and
entering any valid SQL statement into the editor. After you've created your script, save
it with an .SQV file extension. Then use the File | Run menu option to execute your
script.
You need to know a few SQL-VB5 command
syntax rules before you can create your own SQL-VB5 scripts. This is covered in
the next section.
SQL-VB5
Command Syntax
The command syntax for SQL-VB5 is very
similar to standard ANSI SQL syntax. In fact, any valid SQL command is a valid SQL-VB5
command. However, there are a few additional commands in SQL-VB5 that you should
know about.
Three special command words work in SQL-VB5,
but they are not SQL commands. These special commands are used to create, open, and close
Microsoft Jet databases. SQL-VB5 also has a comment command. The comment command
indicates to SQL-VB5 that the information on this line is for comment only and
should not be executed. Finally, each command line must end with a semicolon (;).
The semicolon tells SQL-VB5 where the command line ends. The special command
words, their meanings, and examples are included in Table 13.1.
Table 13.1. Special
SQL-VB5 commands.
SQL-VB5 Command |
Example |
Description |
// |
// this is a comment |
Any line that begins with //
is treated as a comment line and is not processed by the SQL-VB5 Interpreter.
Comments cannot be placed at the end of SQL command lines, but must occupy their own line.
Don't use the single quotation mark for comments as in Visual Basic because that character
is a valid SQL character. Also, you must leave at least one space after the //
for SQL-VB5 to recognize it as a comment marker. |
dbOpen |
dbOpen C:\DATA.MDB; |
The dbOpen command opens a
Microsoft Jet database. SQL-VB5 can only open and process Microsoft Jet-format
databases. A dbOpen command must be executed before any SQL statements are
processed. |
dbMake |
dbMake
C:\NEWDATA.MDB; |
The dbMake command creates a
new, empty Microsoft Jet database on the drive path indicated in the command. When a
database is created using the dbMake command, you do not have to issue a dbOpen
command. |
dbClose |
dbClose; |
The dbClose command closes
the Microsoft Jet database that was opened using the dbOpen or dbMake
command. |
; |
SELECT * FROM Table1; |
The semicolon is used to indicate the
end of a command. Commands can stretch over several lines of text but each command must
always end with a semicolon (;). |
You now have enough information about SQL-VB5 to use it in the rest of the lesson
today. As you go through the examples, you learn more about SQL-VB5 and how you
can create your own SQL scripts. If you want to know more about how SQL-VB5
works, see Appendix A.
Why Use SQL
to Create and Manage Data Tables?
Before you jump into the details of SQL keywords,
let's talk about the advantages of using SQL statements to create and manage your data
tables.
Although Visual Basic offers several powerful
commands for performing the same functions within a Visual Basic program, you might find
that using SQL keywords to perform database management gives you an advantage. By using
SQL statements to create and maintain your database structures, you can easily create
useful documentation on how your databases are structured. Are you trying to debug a
problem at a client site and can't remember how the tables are laid out? If you used a set
of SQL statements to create the tables, you can refer to that script when you are solving
your client's problems.
It is also easy to generate, test, or sample data
tables using SQL statements. If you are working on a database design and are still
experimenting with table layouts and relationships, you can quickly put together an SQL
DDL script, run it through SQL-VB5, and review the results. If, after
experimenting, you find you need a new field in a table, you can alter your existing
script and rerun it. Or you can write a short script that makes only the changes you need,
preserving any data you have loaded into the existing tables.
You can even use SQL statements to load test data
into your new tables. After you have created the tables, you can add SQL statements to
your script that load test data into the columns. This test data can exercise defined
relationships, check for data table integrity, and so on. Using an SQL script to load data
is an excellent way to perform repeated tests on changing data tables. As you make changes
to your table structures, you can use the same data each time until you know you have the
results you are looking for.
Also, you can use the same SQL
statements to create data tables within other database systems, including Microsoft's SQL
Server, Oracle, and others. After you create the test files using Microsoft Access Jet
databases, you can then regenerate the tables for other database engines using the same
SQL statements. This increases the portability of your application and eases the migration
of your data from one database platform to another.
Table
Management SQL Keywords
The type of SQL keywords you learn
today are the table management keywords. These keywords enable you to create new data
tables, alter the structure of existing data tables, and remove existing data tables from
the database.
Designing
New Tables with CREATE TABLE
The CREATE TABLE keyword allows you to
create new tables in an existing database. In its most basic form, the CREATE TABLE
statement consists of three parts: the CREATE TABLE clause; a TableName; and a
list of column names, column types, and column sizes for each column in the new table. The
following example shows a simple CREATE TABLE SQL statement.
CREATE TABLE NewTable (Field1
TEXT(30), Field2 INTEGER); |
This SQL statement creates a data table called
NewTable that has two columns. The column named Field1 is a TEXT column 30 bytes long. The
column named Field2 is an INTEGER column. Notice that no size was designated for the
INTEGER column. Microsoft Access Jet SQL statements only accept size values for TEXT
columns. All other columns are set to a predefined length. See Table 2.1 in Day 2,
"Creating Databases," for a list of the default field lengths for Microsoft
Access Jet data fields.
NOTE: If you omit the size definition for the
TEXT field, Microsoft Access Jet uses the default value of 255 bytes. Because this can
result in rather large tables with empty space, it's a good habit to declare a size for
all TEXT fields.
Test this SQL statement by creating
the SQL script in Listing 13.2 and running it using the SQL-VB5 application.
Start the application and select File | New... to create a new script called SQLVB02.SQV.
Enter the following script commands into Notepad.
Listing
13.2. Creating the SQLVB02.SQV script.
//
// SQLVB02.SQV - Testing SQL Table Management Keywords
//
// create a new database for our tests
dbMake sqlvb02.mdb;
// create a simple table
CREATE TABLE NewTable (Field1 TEXT(30), Field2 INTEGER);
// show the empty table
SELECT * FROM NewTable;
// eof (end of file) |
This script creates a new database, creates a new table in the database, and displays the
empty table in a result set. Use SQL-VB5 to run the script by selecting File |
Run... and locating and loading the SQLVB02.SQV script file. Your results should
appear as shown in Figure 13.7. Please note that this screen is followed by several error
screens which are a result of executing an SQL statement against an empty table. Simply
press OK and move past each message.
Figure 13.7. Results of
the CREATE TABLE statement.
You can also use the PRIMARY KEY
command when you CREATE a data table. This can be done by following the name of
the primary key field with a CONSTRAINT clause. Use SQL-VB5 to edit the SQLVB02.SQV
script so that it sets the Field1 column as a primary key. See Listing 13.3 for an
example.
Listing
13.3. Adding the PRIMARY KEY CONSTRAINT.
//
// testing SQL Table Management Keywords
//
// create a new database for our tests
dbMake sqlvb02.mdb;
// create a simple table
CREATE TABLE NewTable
(Field1 TEXT(30) CONSTRAINT PKNewTable PRIMARY KEY,
Field2 INTEGER);
// show the empty table
SELECT * FROM NewTable;
// eof |
Notice that the CREATE TABLE SQL statement is spread out over more than one line
of text. SQL statements can stretch over several lines, as long as each complete SQL
statement ends with a semicolon. The continued lines need not be indented, but doing so
makes it easier to read the SQL scripts.
You look at the CONSTRAINT
clause in depth a bit later. For now, remember that you can create both primary and
foreign keys in a CREATE TABLE statement.
Modifying
Tables with ALTER TABLE_ADD COLUMN and DROP COLUMN
There are two forms of the ALTER
TABLE statement: the ADD COLUMN form and the DROP COLUMN form. The ADD
COLUMN form enables you to add new columns to an existing table without losing any
data in the existing columns. Edit the SQLVB02.SQV script using SQL-VB5
so that it matches the script in Listing 13.4.
Listing
13.4. Using the ADD COLUMN clause.
//
// testing SQL Table Management Keywords
//
// create a new database for our tests
dbMake sqlvb02.mdb;
// create a simple table
CREATE TABLE NewTable
(Field1 TEXT(30) CONSTRAINT PKNewTable PRIMARY KEY,
Field2 INTEGER);
// add a two new columns
ALTER TABLE NewTable ADD COLUMN Field3 DATE;
ALTER TABLE NewTable ADD COLUMN Field4 CURRENCY;
// show the empty table
SELECT * FROM NewTable;
// eof |
Notice that you had to add two ALTER TABLE statements to add two columns to the
same table. The ALTER TABLE statement can only deal with one column at a time.
Run the SQLVB02.SQV script and inspect the results. Your screen should look
similar to the one in Figure 13.8.
Figure 13.8. Results of
using ALTER TABLE_ADD COLUMN keywords.
NOTE: Note that the ADD COLUMN
clause always adds columns starting at the left-most column in the table. You can always
control the order of the columns in a display using the SELECT_FROM clause (see
Day 8). If you want to control the physical order of the fields, you must add the fields
in a CREATE TABLE statement.
You can also use the ALTER TABLE
statement to remove columns from an existing table without losing data in the unaffected
columns. This is accomplished using the DROP COLUMN clause. Edit SQLVB02.SQV
to match the example in Listing 13.5.
Listing
13.5. Using the DROP COLUMN clause.
//
// testing SQL Table Management Keywords
//
// create a new database for our tests
dbMake sqlvb02.mdb;
// create a simple table
CREATE TABLE NewTable
(Field1 TEXT(30) CONSTRAINT PKNewTable PRIMARY KEY,
Field2 INTEGER);
// add two new columns
ALTER TABLE NewTable ADD COLUMN Field3 DATE;
ALTER TABLE NewTable ADD COLUMN Field4 CURRENCY;
// drop one of the new columns
ALTER TABLE newTable DROP COLUMN Field3;
// show the empty table
SELECT * FROM NewTable;
// eof |
Run the SQLVB02.SQV script and check your results against the screen shown in
Figure 13.9.
Figure 13.9. Results of
the ALTER TABLE_DROP COLUMN keywords.
NOTE: You can also use the ALTER TABLE
statement to ADD or DROP CONSTRAINTs. We cover CONSTRAINTs in
depth later in this chapter.
Deleting
Tables with DROP TABLE
You can use the DROP TABLE
statement to remove a table from the database. This is often used to remove temporary
tables, or it can be used as part of a process that copies data from one table to another
or from one database to another. Edit and save SQLVB02.SQV to match the code
example in Listing 13.6.
Listing
13.6. Using the DROP TABLE clause.
//
// testing SQL Table Management Keywords
//
// create a new database for our tests
dbMake sqlvb02.mdb;
// create a simple table
CREATE TABLE NewTable
(Field1 TEXT(30) CONSTRAINT PKNewTable PRIMARY KEY,
Field2 INTEGER);
// add two new columns
ALTER TABLE NewTable ADD COLUMN Field3 DATE;
ALTER TABLE NewTable ADD COLUMN Field4 CURRENCY;
// drop one of the new columns
ALTER TABLE NewTable DROP COLUMN Field3;
// remove the table from the database
DROP TABLE NewTable;
// show the empty table
SELECT * FROM NewTable;
// eof
|
Save and run the updated SQLVB02.SQV. You should see an SQL error message telling
you that it could not find the table NewTable. This happened because the script executed
the DROP TABLE statement just before the SELECT_FROM statement. The
error message appears in Figure 13.10.
Figure 13.10. Results of
the DROP TABLE statement.
Relationship SQL Keywords
You can create and delete indexes or
constraints on a data table using the SQL keywords CREATE INDEX and DROP
INDEX, and the CONSTRAINT clause of CREATE TABLE and ALTER
TABLE statements. SQL constraints are just indexes with another name. However, CONSTRAINT
clauses are usually used with CREATE TABLE statements to establish relationships
between one or more tables in the same database. INDEX statements are usually
used to add or delete search indexes to existing tables.
Managing
Indexes with CREATE INDEX and DROP INDEX
The CREATE INDEX statement is used to
create a search index on an existing table. The most basic form of the CREATE INDEX
statement is shown in the following line:
CREATE INDEX NewIndex ON NewTable (Field1);
Several variations on the CREATE INDEX
statement allow you to add data integrity to the data table. Table 13.2 shows a list of
the various CREATE INDEX options and how they are used.
Table 13.2. The CREATE
INDEX options.
CREATE INDEX Statement |
Meaning and Use |
CREATE INDEX NewIndex
ON |
Creates a primary key index. A primary
key index |
NewTable(Field1) WITH
PRIMARY |
ensures that each row of the table has
a unique value in the index field. No nulls are allowed in the index field. |
CREATE UNIQUE INDEX
NewIndex |
Creates a unique index on the
designated field. In |
ON NewTable(Field1) |
this example, no two columns could
have the same value, but null values would be allowed. |
CREATE INDEX NewIndex
ON |
Creates an index that is not unique,
but does not |
NewTable (Field1) |
allow null columns. |
WITH DISALLOW NULL |
|
CREATE INDEX NewIndex
ON |
Creates a non-unique index that allows
null |
NewTable (Field1)
WITH |
records in the index column. |
IGNORE NULL |
|
Use SQL-VB5 to create a new
SQL script that contains the code from Listing 13.7. After you enter the code, save the
script as SQLVB03.SQV.
Listing
13.7. Testing the relationship SQL keywords.
//
// sqlvb03.sqv - Test Relationship SQL keywords
//
// create a database
dbMake sqlvb03.mdb;
// create a test table to work with
CREATE TABLE NewTable1
(EmployeeID TEXT(10),
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20),
Department TEXT(10));
// create primary key
CREATE INDEX PKEmployeeID
ON NewTable1(EmployeeID) WITH PRIMARY;
// create unique key column
CREATE UNIQUE INDEX UKLoginName
ON NewTable1(LoginName) WITH IGNORE NULL;
// create non-null column
CREATE INDEX IKJobTitle
ON NewTable1(JobTitle) WITH DISALLOW NULL;
// create multi-column sort key
CREATE INDEX SKDeptSort
ON NewTable1(Department,LastName,FirstName);
// show empty table
SELECT * FROM NewTable1;
// eof |
The preceding SQL script shows several examples of the CREATE INDEX statement.
You can use SQL-VB5 to run this script. Your screen should look similar to the
one in Figure 13.11.
Figure 13.11. Results of SQLVB03.SQV
script.
The code example in Listing 13.7 introduced a naming convention for indexes. This
convention is widely used by SQL programmers. All primary key indexes should start with
the letters PK (PKEmployeeID). All keys created for sorting purposes should begin with the
letters SK (SKDeptSort). All index keys that require unique values should begin with UK
(UKLoginName). All keys that define foreign key relationships should start with FK. (You
learn more about foreign keys in the next section.) Finally, any other index keys should
start with IK (IKJobTitle) to identify them as index keys.
Using the
ASC and DESC Keywords in the INDEX Statement
You can control the index order by adding ASC
(ascending) or DESC (descending) keywords to the CREATE INDEX SQL
statement. For example, to create an index on the LastName column, but listing from
Zilckowicz to Anderson, you use the following CREATE INDEX statement:
CREATE INDEX SKLastName ON
NewTable1(LastName DESC); |
Notice that the DESC goes inside the
parentheses. If you want to control the index order on a multiple column index, you can
use the following CREATE INDEX statement:
CREATE INDEX SKDeptSort ON
NewTable1(Department ASC, LastName DESC); |
If you omit an order word from the CREATE
INDEX clause, SQL uses the default ASC order.
Using
Indexes to Speed Data Access
In Listing 13.7, the index SKDeptSort is a
special index key. This is a sort key index. Sort key indexes can be used to speed data
access while performing single-record lookups (using the Visual Basic Find
method), or for speeding report processing by ordering the data before running a list
report. Sort key indexes are not used to enforce data integrity rules or perform data
entry validation.
Although sort key indexes are very
common in non-relational databases, they are not often used in relational databases. All
the related indexes in a database must be updated by the database engine each time a data
table is updated. If you have created several sort key indexes, you might begin to see a
performance degradation when dealing with large data files or when dealing with remote
(ODBC-connected) databases. For this reason, we do not recommend extensive use of sort key
indexes in your database.
Using
Indexes to Add Database Integrity
You have just about all the possible
indexes created in the SQLVB03.SQV example. Many of the indexes serve as database
integrity enforcers. In fact, only one of the indexes is meant to be used as a tool for
ordering the data (SKDeptSort). All the other indexes in SQLVB03.SQV add database
integrity features to the table. This is an important point. In SQL databases, you have
much more opportunity to build database editing and field-level enforcement into your
database structures than you do with non-relational desktop databases. When you use the
database enforcement options of SQL databases, you can greatly decrease the amount of
Visual Basic code you need to write to support data entry routines. Also, by storing the
database integrity enforcement in the database itself, all other programs that access and
update the database have to conform to the same rules. The rules are no longer stored in
your program; they're stored in the database itself! PRIMARY KEY Enforcement The PRIMARY
KEY index (PKEmployeeID) is familiar to you by now. By defining the index as the
primary key, no record is allowed to contain a NULL value in the column
EmployeeID, and every record must contain a unique value in the EmployeeID column. IGNORE
NULL UNIQUE Enforcement The index key UKLoginName allows records in the table that have
this field blank (IGNORE NULL). However, if a user enters data into this column,
the database checks the other records in the table to make sure that the new entry is
unique (UNIQUE keyword). This shows an excellent method for enforcing uniqueness
on columns that are not required to have input. For example, if you have an input form
that allows users to enter their social security number, but does not require that they do
so, you can ensure that the value for the field is unique by using the IGNORE NULL
and UNIQUE keywords in the INDEX definition. DISALLOW NULL Enforcement
The index key IKJobTitle is another example of using the SQL database engine to enforce
data integrity rules. By defining the IKJobTitle index as DISALLOW NULL, you have
set a data rule that defines this field as a required field. No record can be saved to the
data table unless it has a valid value in the JobTitle column. Notice that you have not
required that the value be unique. That would require every person in the database to have
a unique job title. Instead, you allow duplicate job titles in this column. In real life,
you would probably want to check the value entered here against a list of valid job
titles. That involves creating a foreign key relationship using the CONSTRAINT
keyword. Read the next section for more on CONSTRAINTs.
Managing
Relationships with CONSTRAINTs
CONSTRAINTs are really the same as indexes
from the standpoint of SQL statements. The CONSTRAINT keyword is used to create
indexes that add data integrity to your database. You must use the CONSTRAINT
keyword with the CREATE TABLE or ALTER TABLE SQL statement. There is no
such thing in Microsoft Access Jet SQL as CREATE CONSTRAINT.
There are three forms of the CONSTRAINT
clause:
- PRIMARY KEY
- UNIQUE
- FOREIGN KEY
Microsoft Access SQL syntax does not
allow you to use the IGNORE NULL or DISALLOW NULL keywords within the CONSTRAINT
clause. If you want to create data integrity indexes that include the IGNORE NULL
or DISALLOW NULL keywords, you have to use the CREATE INDEX keyword to
define your index. Using the PRIMARY KEY CONSTRAINT The most commonly used CONSTRAINT
clause is the PRIMARY KEY CONSTRAINT. This is used to define the column (or set
of columns) that contains the primary key for the table. The SQL-VB5 script in
Listing 13.8 creates a new database and a single table that contains two fields, one of
which is the primary key column for the table. The other field is a MEMO field. MEMO
fields can contain any type of free-form text and cannot be used in any CONSTRAINT
or INDEX definition.
Listing
13.8. Testing the PRIMARY KEY CONSTRAINT.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
JobDesc MEMO
);
// show the table
SELECT * FROM JobsTable;
// eof
|
Enter this code into the SQL-Visual Basic editor, save the script as SQLVB04.SQV
and execute it. You see a simple table that shows two fields. See Figure 13.12 for an
example.
Figure 13.12. Defining
the PRIMARY KEY CONSTRAINT.
The SQL script in Listing 13.9 performs the same task, except it uses the CREATE INDEX
keyword to define the primary key index.
Listing
13.9. Using CREATE INDEX to define the PRIMARY KEY.
//
// create index using CREATE INDEX keywords
//
// create database
dbMake sqlvb04.mdb;
// create table
CREATE TABLE JobsTable
(JobTitle TEXT(20),
JobDesc MEMO
);
// create index
CREATE INDEX PKJobTitle ON JobsTable(JobTitle) WITH PRIMARY;
// eof |
Although the code examples in Listing 13.8 and Listing 13.9 both perform the same task,
the first code example (Listing 13.8) is the preferred method for creating primary key
indexes. Listing 13.8 documents the creation of the index at the time the table is
created. This is easier to understand and easier to maintain over time. It is possible to
create primary key indexes using the CREATE INDEX statement, but this can lead to
problems. If you attempt to use the CREATE INDEX_PRIMARY KEY statement on a table
that already has a primary key index defined, you get a database error. It is best to
avoid this error by limiting the creation of primary key indexes to CREATE TABLE
statements. Using the UNIQUE KEY CONSTRAINT Another common use of the CONSTRAINT
clause is in the creation of UNIQUE indexes. By default, the index key created
using the UNIQUE CONSTRAINT clause allows null entries in the identified columns.
However, when data is entered into the column, that data must be unique or the database
engine returns an error message. This is the same as using the IGNORE NULL
keyword in the CREATE INDEX statement. You should also note that you cannot use
the DISALLOW NULL keywords when creating a UNIQUE CONSTRAINT clause. By
default, all keys created using the UNIQUE CONSTRAINT are IGNORE NULL
index keys.
The SQL script in Listing 13.10 shows
a new column in the JobsTable data table that was created in the last SQL-VB5 script.
The new column, BudgetCode, is defined as an optional data column that must contain unique
data. Update your version of the SQLVB04.SQV script, save it, and execute it.
Your result set should resemble the one shown in Figure 13.13.
Listing
13.10. Adding a UNIQUE CONSTRAINT.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetCode TEXT(10) CONSTRAINT UKJobCode UNIQUE,
JobDesc MEMO
);
// show table
SELECT * FROM JobsTable;
// eof |
Figure 13.13. Defining a UNIQUE
CONSTRAINT index.
You can use the UNIQUE CONSTRAINT clause in a multicolumn index. This is
especially handy if you have a data table containing more than one field that must be
evaluated when deciding uniqueness. For example, what if the preceding data table, in
addition to BudgetCode, had BudgetPrefix and BudgetSuffix, too? You can make sure that the
combination of the three fields is always unique by building a multicolumn CONSTRAINT
clause. Use the code example in Listing 13.11 as a guide. Update your SQLVB04.SQV
script to match the example in Listing 13.11 and execute it to make sure you have written
the syntax correctly.
Listing
13.11. Defining a multicolumn UNIQUE CONSTRAINT.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT UKBudget UNIQUE (BudgetPrefix,BudgetCode,BudgetSuffix)
);
// show table
SELECT * FROM JobsTable;
// eof |
Once the script has executed, your screen should look similar to the one in Figure 13.14.
Figure 13.14. The results
of a multicolumn CONSTRAINT clause.
You should also be aware of an important difference between the single-column and
multicolumn CONSTRAINT clause formats. Notice that when you are defining a
single-column CONSTRAINT, you place the CONSTRAINT clause directly after
the column definition without a comma between the column type and the CONSTRAINT
keyword. In the multicolumn CONSTRAINT clause, you separate the CONSTRAINT
clause with a comma and enclose the column names within parentheses. Mixing these two
formats can lead to frustration when you are trying to debug an SQL script!
TIP: Think of it this way. In the case of a
single-column CONSTRAINT, these are additional qualifiers of the column; the
constraint belongs within the column definition. A multicolumn CONSTRAINT,
however, is a standalone definition that is not an extension of any one column definition.
For this reason, multicolumn constraints are treated as if they are on an equal level with
a column definition. They stand alone in the column list.
Using the FOREIGN KEY_REFERENCES Relationship The
most powerful of the CONSTRAINT formats is the FOREIGN KEY_REFERENCES
format. This format is used to establish relationships between tables. Commonly, a FOREIGN
KEY relationship is established between a small table containing a list of valid
column entries (usually called a validation table) and another table. The second table
usually has a column defined with the same name as the primary key column in the
validation table. By establishing a foreign key relationship between the two files, you
can enforce a database rule that says the only valid entries in a given table are those
values that already exist in the primary key column of the validation table. Once again,
you are using the database engine to store data integrity rules. This reduces your volume
of Visual Basic code and increases database integrity.
Let's use the script from Listing
13.11 (SQLVB04.SQV) to create a foreign key relationship. You already have a
table defined--JobsTable. This is an excellent example of a validation table. It has few
fields and has a single column defined as the primary key. Now let's add another
table--the EmpsTable. This table holds basic information about employees, including their
respective job titles. Listing 13.12 shows modifications to SQLVB04.SQV that
include the definition of the EmpsTable data table.
Listing
13.12. Adding a PRIMARY KEY CONSTRAINT to the EmpsTable.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT UKBudget UNIQUE (BudgetPrefix,BudgetCode,BudgetSuffix)
);
// create a test table to work with
CREATE TABLE EmpsTable
(EmployeeID TEXT(10) CONSTRAINT PKEmployeeID PRIMARY KEY,
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20),
Department TEXT(10)
);
// show empty table
SELECT * FROM JobsTable;
SELECT * FROM EmpsTable;
// eof |
The SQL-VB5 script in Listing 13.12 defines the EmpsTable with only one CONSTRAINT--that
of the PRIMARY KEY index. Now let's define a relationship between the
EmpsTable.JobTitle column and the JobsTable.JobTitle column. You do this by using the FOREIGN
KEY CONSTRAINT syntax. The modified SQLVB04.SQV is shown in Listing 13.13.
Listing
13.13. Adding the FOREIGN KEY_REFERENCES CONSTRAINT.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT UKBudget UNIQUE (BudgetPrefix,BudgetCode,BudgetSuffix)
);
// create a test table to work with
CREATE TABLE EmpsTable
(EmployeeID TEXT(10) CONSTRAINT PKEmployeeID PRIMARY KEY,
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20) CONSTRAINT FKJobTitle REFERENCES
JobsTable(JobTitle),
Department TEXT(10)
);
// show empty table
SELECT * FROM JobsTable;
SELECT * FROM EmpsTable;
// eof |
Notice that the exact SQL syntax for single-column foreign key indexes is
CONSTRAINT IndexName REFERENCES
Tablename(ColumnName) |
As long as the column name you are referencing
defines the PRIMARY KEY of the referenced table, you can omit the (ColumnName)
portion of the CONSTRAINT clause. However, it is good programming practice to
include the column name for clarity.
Use the SQL-VB5 editor window to load SQLVB04.SQV.
Modify the script to match the code in Listing 13.13, save it, and run the script. Your
screen should resemble Figure 13.15.
Figure 13.15. A foreign
key constraint cascades the related tables on-screen.
What you have defined here is a rule that tells the Microsoft Jet database engine that,
any time a user enters data into the EmpsTable.JobTitle column, the engine should refer to
the JobsTable.JobTitle column to make sure that the value entered in EmpsTable.JobTitle
can be found in one of the rows of JobsTable.JobTitle. If not, return an error message to
the user and do not save the record to the data table. All that is done without writing
any input validation code at all!
You can set up foreign key relations
between any two columns in any two tables. They need not have the same column name, but
they must have the same data type. For example, you can add a table to the SQLVB04.MDB
database that holds information about job titles and pay grades. However, in this table
the column that holds the job title is called JobName. Enter the script in Listing 13.14,
save it, and execute it. See Figure 13.16 for a guide.
Listing
13.14. Creating a foreign key relationship on unmatched field names.
//
// sqlvb04.sqv - Test CONSTRAINT SQL keyword
//
// create a database
dbMake sqlvb04.mdb;
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT UKBudget UNIQUE (BudgetPrefix,BudgetCode,BudgetSuffix)
);
// create job pay grade table
CREATE TABLE PayGrades
(GradeID TEXT(5) CONSTRAINT PKGradeID PRIMARY KEY,
JobName TEXT(20) CONSTRAINT FKJobName REFERENCES
JobsTable(JobTitle),
PayMin CURRENCY,
PayMax CURRENCY
);
// create a test table to work with
CREATE TABLE EmpsTable
(EmployeeID TEXT(10) CONSTRAINT PKEmployeeID PRIMARY KEY,
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20) CONSTRAINT FKJobTitle REFERENCES
JobsTable(JobTitle),
Department TEXT(10)
);
// show empty table
SELECT * FROM JobsTable;
SELECT * FROM PayGrades;
SELECT * FROM EmpsTable;
// eof |
Notice that the column PayGrades.JobName does not have the same name as its referenced
column (JobsTable.JobTitle). You can still define a foreign key relationship for these
columns. This relationship operates exactly the same as the one defined for
EmpsTable.JobTitle and JobsTable.JobTitle.
It is also important to point out the order in which
you must create tables when you are establishing foreign key constraints. You must always
create the referenced table before you refer to it in a CONSTRAINT clause.
Failure to adhere to this rule results in a database error when you run your SQL-VB5 script.
SQL must see that the table exists before a foreign key reference to it can be
established.
Figure 13.16. Results of
a foreign key constraint on unmatched column names.
It is also possible to create a multicolumn foreign key constraint. When you create
multicolumn foreign key constraints, you must reference the same number of columns on each
side of the relationship. For example, if you have a primary key index called PKBudgetCode
that contains three columns, any foreign key constraint you define in another table that
references PKBudgetCode must also contain three columns.
The example in Listing 13.15 shows an
added foreign key constraint in the JobsTable. This constraint sets up a relationship
between the Budget columns in the BudgetTrack table and JobsTable. Make the changes to the
SQLVB04.SQV script and execute it to check for errors. See Figure 13.17 to
compare your results.
Listing
13.15. Creating a multicolumn foreign key constraint.
// create a database
dbMake sqlvb04.mdb;
// create budget tracking file
CREATE TABLE BudgetTrack
(BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
CONSTRAINT PKBudgetCode PRIMARY KEY
(BudgetPrefix,BudgetCode,BudgetSuffix),
AnnBudgetAmt CURRENCY,
YTDActualAmt CURRENCY
);
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT FKBudget
FOREIGN KEY (BudgetPrefix,BudgetCode,BudgetSuffix)
REFERENCES BudgetTrack
);
// create job pay grade table
CREATE TABLE PayGrades
(GradeID TEXT(5) CONSTRAINT PKGradeID PRIMARY KEY,
JobName TEXT(20) CONSTRAINT FKJobName REFERENCES
JobsTable(JobTitle),
PayMin CURRENCY,
PayMax CURRENCY
);
// create a test table to work with
CREATE TABLE EmpsTable
(EmployeeID TEXT(10) CONSTRAINT PKEmployeeID PRIMARY KEY,
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20) CONSTRAINT FKJobTitle REFERENCES
JobsTable(JobTitle),
Department TEXT(10)
);
// show empty table
SELECT * FROM JobsTable;
SELECT * FROM EmpsTable;
SELECT * FROM PayGrades;
SELECT * FROM BudgetTrack;
// eof |
Figure 13.17. The results of adding a multicolumn
foreign key constraint.
Notice that the syntax for adding multicolumn foreign key constraints differs from that
used when creating single-column foreign key relationships. When creating multicolumn
foreign key relationships, you have to actually use the keywords FOREIGN KEY.
Also, you list the columns in parentheses in the same order in which they are listed in
the referenced key for the referenced table. Using ALTER TABLE to ADD and DROP Constraints
You can also use the ALTER TABLE statement to add constraints or drop constraints
from existing data tables. The code example in Listing 13.16 adds a new constraint to an
existing table, and then removes it. You should be careful adding or dropping constraints
outside of the CREATE TABLE statement. Although SQL allows you to do this, it can
often lead to data integrity errors if data already exists within the target table. We
recommend that you only establish CONSTRAINTs at the time you create the table
using the CREATE TABLE statement.
Listing
13.16. Using ALTER TABLE to ADD and DROP constraints.
// create a database
dbMake sqlvb04.mdb;
// create budget tracking file
CREATE TABLE BudgetTrack
(BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
CONSTRAINT PKBudgetCode PRIMARY KEY
(BudgetPrefix,BudgetCode,BudgetSuffix),
AnnBudgetAmt CURRENCY,
YTDActualAmt CURRENCY
);
// create jobs title table
CREATE TABLE JobsTable
(JobTitle TEXT (20) CONSTRAINT PKJobTitle PRIMARY KEY,
BudgetPrefix TEXT(5),
BudgetCode TEXT(10),
BudgetSuffix TEXT(5),
JobDesc MEMO,
CONSTRAINT FKBudget
FOREIGN KEY (BudgetPrefix,BudgetCode,BudgetSuffix)
REFERENCES BudgetTrack
);
// create job pay grade table
CREATE TABLE PayGrades
(GradeID TEXT(5) CONSTRAINT PKGradeID PRIMARY KEY,
JobName TEXT(20) CONSTRAINT FKJobName REFERENCES
JobsTable(JobTitle),
PayMin CURRENCY,
PayMax CURRENCY
);
// create a test table to work with
CREATE TABLE EmpsTable
(EmployeeID TEXT(10) CONSTRAINT PKEmployeeID PRIMARY KEY,
LastName TEXT(30),
FirstName TEXT(30),
LoginName TEXT(15),
JobTitle TEXT(20) CONSTRAINT FKJobTitle REFERENCES
JobsTable(JobTitle),
Department TEXT(10)
);
// use alter table to add and drop a constraint
ALTER TABLE EmpsTable ADD CONSTRAINT FKMoreJobs
FOREIGN KEY (JobTitle) REFERENCES JobsTable(JobTitle);
ALTER TABLE EmpsTable DROP CONSTRAINT FKMoreJobs;
// show empty table
SELECT * FROM JobsTable;
SELECT * FROM EmpsTable;
SELECT * FROM PayGrades;
SELECT * FROM BudgetTrack;
// eof |
In today's lesson, you saw SQL keywords that create and alter tables and establish table
indexes and relationship constraints. Now you are ready for tomorrow's lesson, in which
you learn the SQL keywords that you can use to add data to the tables you have created.
You'll also see keywords that you can use to copy tables, including the data.
Summary
In today's lesson you learned how to create, alter,
and delete database table structures using DDL (Data Definition Language) SQL keywords.
You also learned that using DDL statements to build tables, create indexes, and establish
relationships is an excellent way to automatically document table layouts. You learned how
to maintain database structures using the following DDL keywords:
- CREATE TABLE enables you to create entirely
new tables in your existing database.
- n DROP TABLE enables you to
completely remove a table, including any data that is already in the table.
- ALTER TABLE enables you to ADD a
new column or DROP an existing column from the table without losing existing data
in the other columns.
- CREATE INDEX and DROP INDEX enable
you to create indexes that can enforce data integrity or speed data access.
- The CONSTRAINT clause can be added to the CREATE
TABLE or ALTER TABLE statement to define relationships between tables using
the FOREIGN KEY clause.
Quiz
- 1. What are the benefits of using SQL to
create and manage data tables?
2. What is the format of the CREATE TABLE statement?
3. What is the default size of a Microsoft Jet TEXT field?
4. What SQL statement do you use to add a column to a table? What is its format?
5. What SQL statement do you use to remove a table from a database? What is the
format of this statement?
6. What SQL statement creates an index to a data table?
7. What are the three forms of the CONSTRAINT clause?
Exercise
You have been assigned the responsibility of
building a database of customers for your company. After careful review of the business
processes and interviews with other users, you have determined that the following data
must be maintained for the Customer database:
Table |
Name |
Field Type |
CustomerType |
CustomerType |
TEXT(6) |
|
Description |
TEXT(30) |
Customers |
CustomerID |
TEXT(10) |
|
Name |
TEXT(30) |
|
CustomerType |
TEXT(6) |
|
Address |
TEXT(30) |
|
City |
TEXT(30) |
|
State |
TEXT(30) |
|
Zip |
TEXT(10) |
|
Phone |
TEXT(14) |
|
FAX |
TEXT(14) |
Use SQL-VB5 to build this structure. Include a primary key for each table and an
index on Zip in the Customers table. Include any foreign key relationships that you think
would increase database integrity. Name your database CH13EX.MDB. (You can use
any path that you like for the .MDB file).
|