Day 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:

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 book 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 book). 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 BOOKS5.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\books5.mdb;

// open some tables to view
SELECT * FROM Authors WHERE AUID<7;
// SELECT * FROM Titles;
// SELECT * FROM Publishers;
// SELECT * FROM BookSales;
// 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:

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:

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).