Chapter 11
Table Management and Indexes
Introduction
In this chapter, you will learn how to modify a column in an existing table, delete a table, and improve data retrieval time using indexes.
Definitions
ALTER TABLE — Used to modify table definitions in an existing table.
DISALLOW NULL — Used to prevent null data from being inserted into a column.
IGNORE NULL — Used to cause null data in a table to be ignored for an index.
INDEX — Sorts and saves the values of a column in a different location on the computer with a pointer to the presorted records.
PRIMARY — Used to designate a column as a primary key.
UNIQUE — Used to ensure that only unique, non-repeating values are inserted in an indexed column.
After a table is created it is often necessary to modify the columns defined in it. The ALTER TABLE statement is used to modify table definitions in an existing table. The ALTER TABLE statement can be used to add a column to a table, change a column, or remove a column from a table. It can also be used to modify (add/remove) constraints and to set a default value for a column. In Chapter 3, you learned how to use the ALTER TABLE statement to modify constraints in existing tables.
Note: You can only modify one column at a time. It is not recommended that you modify a table once it contains data.
Adding a Column to an Existing Table
To add a column to an existing table use the ADD keyword in the ALTER TABLE statement and specify a table name, column name, data type, and a field size if necessary.
Look at the following syntax for adding a column to an existing table:
ALTER TABLE Tablename
ADD ColumnName Datatype (Field size);
Note: In version SQL-92 or higher you can use the following alternate syntax:
ALTER TABLE Tablename
ADD COLUMN ColumnName Datatype (Field size);
This syntax implements the additional COLUMN keyword in the ALTER TABLE statement.
Take a look at Example 1, which adds an additional column to an existing table.
Example 1
Figure 11-1. Numbers table
Suppose you want to add a column named ColumnFour to the existing Numbers table in Figure 11-1. Look at the following script:
ALTER TABLE Numbers
ADD ColumnFour INTEGER;
This script uses the ALTER TABLE keywords to instruct Microsoft Access to modify the Numbers table. The ADD keyword is used to add a new column named ColumnFour with an INTEGER data type. Look at the results in Figure 11-2.
Figure 11-2. Results (output)
Changing a Column
The ALTER TABLE statement can also be used to change a column’s name, data type, or field size. Keep in mind that you cannot change the name of a column unless you first remove the column and then add the new column. You’ll learn how to remove (delete) a column later in this chapter. Additionally, it is important to note that changes to columns that contain data may cause a loss of data. Use caution when modifying data types and field sizes in columns that contain data.
To change a column’s data type or field size, use the ALTER COLUMN keywords in the ALTER TABLE statement and specify a table name, column name, data type, and a field size if necessary. Look at the following syntax for changing a column’s data type and field size:
ALTER TABLE Tablename
ALTER COLUMN ColumnName Datatype (Field size);
Now take a look at Example 2, which modifies the data type of an existing table.
Example 2
Suppose you want to change the data type for the newly created column (ColumnFour) in the Numbers table in Example 1. You want to change the data type from an INTEGER data type to a CHAR data type and you want to add a field size of 3. Look at the following script:
ALTER TABLE Numbers
ALTER COLUMN ColumnFour CHAR (3);
This script uses the ALTER TABLE statement to modify the Numbers table. The ALTER COLUMN keywords are used to specify a new data type (CHAR) and field size (3) for the ColumnFour column.
To view the results from the ALTER TABLE statement in Microsoft Access 2007, select Tables from the View drop-down window on the left. Right-click on the Numbers table and select Design View.
Look under the Field Name column to view a column and under the Datatype column to view a data type for a column. The General tab near the bottom of the screen shows information pertaining to an individual column.
Note: The CHAR data type is a TEXT data type.
Setting a Default Value for a Column
SQL-92 enables you to additionally use the ALTER TABLE statement to set a default value for a column each time a new record is entered in a table and no value is specified for that column.
To set a default value for a column, use the DEFAULT keyword in the ALTER TABLE statement. Additionally, you can specify a table name, column name, data type, field size if necessary, and a value to default to. Look at the following syntax for setting a default value:
ALTER TABLE Tablename
ALTER COLUMN ColumnName Datatype (Field size) DEFAULT Defaultvalue
Refer to Example 3 to see an example of setting a default value.
Example 3
Suppose you want to set a default value of 10 for the ColumnFour column in the Numbers table each time a new record is entered and no value is specified for the ColumnFour column. Look at the following script:
ALTER TABLE Numbers
ALTER COLUMN ColumnFour CHAR (3) DEFAULT 10
The preceding script uses the DEFAULT keyword to set the ColumnFour column in the Numbers table to 10 each time a new record is entered and no value is specified for the ColumnFour column. Look at the results in Figure 11-3. After a new record was entered into the Numbers table with no value for the fourth column, the ColumnFour column defaulted to 10. Note that this does not affect existing columns, just new ones.
Figure 11-3. Results (output)
Removing a Column from a Table
To remove a column from a table, use the DROP keyword in the ALTER TABLE statement and specify a table name and column name. Look at the following syntax for removing a column:
ALTER TABLE Tablename
DROP ColumnName;
Look at Example 4, which removes a column from an existing table.
Example 4
Suppose you want to remove the column created in Example 1 (ColumnFour). Look at the following script:
ALTER TABLE Numbers
DROP ColumnFour;
The preceding script removes the ColumnFour column from the Numbers table. Look at the results in Figure 11-4.
Figure 11-4. Results (output)
Removing a Table
To remove an entire table, you do not use the ALTER TABLE keywords but rather DROP TABLE. Look at the following syntax for removing a table:
DROP TABLE Tablename;
In the syntax, the DROP TABLE keywords are used with the name of the table to delete.
Improving Data Retrieval Time Using Indexes
Indexes enable you to reduce your data retrieval time during the execution of a query by presorting and ordering the data in a field using external pointers to optimally group the records. Indexes help to retrieve records much faster because the DBMS must only search through the presorted grouped records rather than search through every record in a table until a match is found. For example, when you pick up a dictionary, you narrow your selection by looking through pages that are alphabetized and have tabs separating the letters as opposed to flipping through every page for unalphabetized entries. Be aware that although indexes reduce your data retrieval time, they can reduce speed on updates on columns that are indexed because the index may possibly have to be rebuilt. Additionally, if you have too many indexes, retrieval time can increase due to the operations associated with maintaining an index. Indexed data can use up a lot of memory, so make sure you decide which columns would benefit from an index and which would not. For example, columns that contain non-unique data will not benefit as much as columns that contain unique data.
In most databases indexes are stored as separate files or tables. Access is a bit unique since the indexes are stored along with the table in a single file.
Note: The primary key column of a table is a type of index because it is always physically sorted in ascending order.
Take a look at the following syntax for creating an index:
CREATE INDEX Indexname
ON Tablename (ColumnName [ASC | DESC]);
As you can see in the above syntax, to create an index you must use the CREATE INDEX keywords. Following these keywords you must specify a unique name for your index. Additionally, you must use the ON keyword to specify the table name, column name, and sort order (ascending or descending).
Note: If you do not specify a sort order in your index, it will automatically default to ascending order.
Index Options
There are four options available to you when creating an index. These options are available in an additional clause called the WITH clause: UNIQUE, PRIMARY, DISALLOW NULL, and IGNORE NULL. The WITH clause is used to enforce validation rules. Table 11-1 explains the four options used in the WITH clause.
Table 11-1. WITH clause options
Options | Description |
UNIQUE | Used to ensure that only unique, non-repeating values are inserted in an indexed column. |
PRIMARY | Used to designate a column as a primary key. |
DISALLOW NULL | Used to prevent null data from being inserted into a column. |
IGNORE NULL | Used to cause null data in a table to be ignored for an index. (Records with a null value in the declared field will not be counted in the index.) |
Take a look at Example 5, which shows how to create an index.
Creating an Index
Example 5
Figure 11-5. Products table
Suppose you want to create a unique index that will not allow nulls in the ProductName column in the Products table in Figure 11-5. Look at the following script:
CREATE UNIQUE INDEX ProductNameIdx
ON Products (ProductName)
WITH DISALLOW NULL;
This script creates an index named ProductNameIdx. The index is defined on the ProductName column in the Products table. The UNIQUE keyword is used to ensure that only unique, non-repeating values are inserted into the ProductName column, while the DISALLOW NULL keywords are used to prevent null data from being inserted into the ProductName column. The DISALLOW NULL keywords are similar to the NOT NULL keywords used in the CREATE TABLE statement.
Indexing in Descending Order
Indexes default to ascending order but you can also sort a column in descending order. To sort by descending order, you simply need to add the DESC keyword to your SQL script. Take a look at Example 6.
Example 6
Suppose you want to create an index that sorts the ProductName column in descending order. Take a look at the following script:
CREATE UNIQUE INDEX ProductNameIdx2
ON Products (ProductName DESC)
WITH DISALLOW NULL;
This script creates an index named ProductNameIdx2. The index is defined on the ProductName column in the Products table. The DESC keyword is defined immediately following the ProductName column. The DESC keyword ensures that the data in the ProductName column is stored in descending order.
The UNIQUE keyword is used to ensure there are no repeating values. The DISALLOW NULL keywords are used to prevent null values in the ProductName column.
Viewing and Editing Indexes
To view or edit an index for a particular table, open a table in Design view. To open a table in Design view, choose Tables from the View drop-down in Access 2007. Right-click on the name of the table you want to open and choose Design View. Finally, click the Index button to view all the indexes for a table. Take a look at Figure 11-6.
Figure 11-6. Viewing an index
The Indexes dialog box shows the name of the index, the field name the index is associated with, and the sort order of the index. Additionally, when you click on the name of an index, the index properties are displayed. You can edit an index by modifying values in the dialog box.
You can also create a new index based on multiple fields in a table. This is a common process when you want a unique index but only the values in multiple fields might constitute a unique value.
Alternately, you can create an index while in Design view for a table. When a field is selected, go to the field properties and select the Indexed property. You can select No, the field is not indexed; Yes (Duplicates OK), where the index is not a unique value; or Yes (No Duplicates), where the values in the indexed field have to be unique.
Figure 11-7. Manually creating an index
If you try to make a field that already has data an indexed field with no duplicates and the data does not consist of unique values, the system will not let you save the new index.
Deleting an Index
To delete an index from a table, you must use the DROP INDEX keywords. Look at the following syntax for deleting an index:
DROP INDEX Indexname
ON Tablename;
Example 7
To delete the index named ProductNameIdx created in Example 5, type the following script:
DROP INDEX ProductNameIdx
ON Products;
This script deletes the index named ProductNameIdx from the Products table.
Note: When you delete a table, all indexes pertaining to that table are deleted as well.
Summary
In this chapter, you learned how to modify columns in an existing table, delete a table, and improve data retrieval time by the use of indexes.
Quiz 11
1. True or False. The DISALLOW NULL option is used in the WITH clause.
2. Which option is used in the WITH clause to cause null data in a table to be ignored for an index?
3. True or False. The DELETE TABLE keywords are used to delete or remove an index.
4. True or False. The ALTER TABLE keywords are used to modify columns in an existing table.
5. What keywords are used in the ALTER TABLE statement to change a column’s data type or field size?
Project 11
1. Add a column named NewColumn to the Numbers table in Figure 11-1. Additionally, add a CHAR data type with a field size of 3.
2. Create a unique index named NewColumnIdx for the NewColumn column you created in the Numbers table.