|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 13
  - Creating Databases with SQL
    - Using the SQL-VB5 Interpreter
      - Loading and Running the SQL-VB5 Interpreter
        - Figure 13.1.
        - Figure 13.2.
        - Figure 13.3.
        - Figure 13.4.
        - Figure 13.5.
      - Creating and Editing SQL-VB5 Scripts
    - Listing 13.1. Modifying a SQL-VB5 script.
      - Figure 13.6.
    - SQL-VB5 Command Syntax
    - Why Use SQL to Create and Manage Data Tables?
    - Table Management SQL Keywords
      - Designing New Tables with CREATE TABLE
    - Listing 13.2. Creating the SQLVB02.SQV script.
      - Figure 13.7.
    - Listing 13.3. Adding the PRIMARY KEY CONSTRAINT.
      - Modifying Tables with ALTER TABLE_ADD COLUMN and DROP COLUMN
    - Listing 13.4. Using the ADD COLUMN clause.
      - Figure 13.8.
    - Listing 13.5. Using the DROP COLUMN clause.
      - Figure 13.9.
    - Deleting Tables with DROP TABLE
    - Listing 13.6. Using the DROP TABLE clause.
      - Figure 13.10.
    - Relationship SQL Keywords
      - Managing Indexes with CREATE INDEX and DROP INDEX
    - Listing 13.7. Testing the relationship SQL keywords.
      - Figure 13.11.
    - Using the ASC and DESC Keywords in the INDEX Statement
      - Using Indexes to Speed Data Access
      - Using Indexes to Add Database Integrity
      - Managing Relationships with CONSTRAINTs
    - Listing 13.8. Testing the PRIMARY KEY CONSTRAINT.
      - Figure 13.12.
    - Listing 13.9. Using CREATE INDEX to define the PRIMARY KEY.
    - Listing 13.10. Adding a UNIQUE CONSTRAINT.
      - Figure 13.13.
    - Listing 13.11. Defining a multicolumn UNIQUE CONSTRAINT.
      - Figure 13.14.
    - Listing 13.12. Adding a PRIMARY KEY CONSTRAINT to the EmpsTable.
    - Listing 13.13. Adding the FOREIGN KEY_REFERENCES CONSTRAINT.
      - Figure 13.15.
    - Listing 13.14. Creating a foreign key relationship on unmatched field names.
      - Figure 13.16.
    - Listing 13.15. Creating a multicolumn foreign key constraint.
      - Figure 13.17.
    - Listing 13.16. Using ALTER TABLE to ADD and DROP constraints.
    - Summary
    - Quiz
    - Exercise

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

- 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 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 CONSTRAINT`s. We cover `CONSTRAINT`s 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 `CONSTRAINT`s.

#### Managing Relationships with CONSTRAINTs

`CONSTRAINT`s 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 `CONSTRAINT`s 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).

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.