18. Maintaining Tables

Keywords Introduced

CREATE TABLE • DROP TABLE • CREATE INDEX • DROP INDEX

With this chapter, we change our focus from data retrieval and modification to design. Up until now, we’ve assumed that tables simply exist and are available to any interested user. However, in the normal course of events, someone must create tables before their data can be accessed. We therefore turn to the question of how to create and maintain tables.

We previously touched on a few of the topics we’ll be addressing, such as primary and foreign keys, but we now want to delve into these areas in greater detail, and also address the related topic of table indexes.

Data Definition Language

Back in Chapter 1, we mentioned the three main components of the SQL language: DML (Data Manipulation Language), DDL (Data Definition Language), and DCL (Data Control Language). Up until now, most of what we’ve talked about has been DML. DML statements allow you to manipulate data in relational databases by retrieval, insertion, deletion, or updating. This is handled by the SELECT, INSERT, DELETE, and UPDATE statements.

Although our focus has been on DML, we have already seen a few instances of DDL (Data Definition Language). The CREATE VIEW and CREATE PROCEDURE statements we encountered in Chapters 13 and 16 are DDL, as are the related ALTER and DROP versions of those statements.

CREATE VIEW and CREATE PROCEDURE statements are DDL because they only allow you to manipulate the structure of a database. They have nothing to do with the data they contain.

In this chapter, we’ll provide a brief overview of a few additional DDL statements that can be used to create and modify tables and indexes.

Each database has a different way of organizing its objects, and therefore has different available DDL statements. For example, MySQL has 12 different CREATE statements for these types of objects: Databases, Events, Functions, Indexes, Logfile Groups, Procedures, Servers, Spatial Reference Systems, Tables, Tablespaces, Triggers, and Views.

Oracle has more than 40 different CREATE commands for the object types in its database. Microsoft SQL Server has more than 60 different CREATE commands for its object types.

In truth, most modifications to database objects, such as views and tables, can be accomplished through the visual GUI (graphical user interface) that each software vendor provides to administer their software. It is often not necessary to learn any DDL at all, because it can often be handled with the software GUI.

However, it’s useful to be aware of at least the existence of a few key statements for manipulating data objects. We’ve already seen some statements that allow us to modify views and stored procedures. In this chapter, we’ll cover some of the possibilities for modifying tables and indexes via DDL.

Table Attributes

In the first two chapters, we briefly discussed a few attributes of database tables, such as primary keys, foreign keys, datatypes, and auto-increment columns. As mentioned, SQL DDL provides CREATE statements for many types of database objects. In Chapters 13 and 16, we talked about the CREATE PROCEDURE and CREATE VIEW statements that handle stored procedures and views.

We’ll now bring our attention back to tables. Tables are perhaps the primary and most essential object type in a database. Without tables, nothing else really matters. All the data in a database is physically stored in tables. Most other object types relate to tables in one way or another. Views provide a virtual view of tables. Stored procedures generally act upon data in tables. Functions allow for special rules for the manipulation of data in tables.

We’ll focus here on how tables can be created initially. A large number of attributes can be associated with table definitions. We’ll give an overview of some of the more important attributes and discuss what they mean.

The subject of table attributes is also related to the larger topic of database design, which will be addressed in the next chapter. For now, we want to focus on the mechanics of what can be done with the tables themselves.

The specifics of how tables can be designed and altered varies widely among Microsoft SQL Server, MySQL, and Oracle. We’ll talk primarily about those attributes common to tables in all three databases.

Table Columns

Tables are defined as containing any number of columns. Each column has a variety of attributes specific to that column. The first and most obvious attribute is the column name. Each column must be given a name unique to that table.

A second attribute of columns is the datatype, a subject that was addressed in Chapter 1. We’ve already described some notable datatypes in three main categories: numeric, character, and date/time. The datatype is a key determinant of the type of data each column can contain.

A third attribute of columns is whether or not it is defined as an auto-increment column. We briefly introduced this attribute type in Chapters 1 and 2 and discussed it further in the preceding chapter about modifying data. Basically, an auto-increment column means that the column is automatically assigned a numeric value, in ascending sequence, as each row is added to the table. Auto-increment columns are often used with primary keys but can also be assigned to an ordinary column.

Note that the term auto-increment is specific to MySQL. Microsoft uses the term identity to refer to the same type of attribute.

A fourth column attribute is whether or not the column is allowed to contain NULL values. The default is to allow NULL values. If you don’t want to allow a column to contain NULLs, it is normally specified via a NOT NULL keyword applied to the column description.

The final column attribute we’ll mention is whether the column is assigned a default value. A default value is automatically assigned to the column if no value for that column is provided when a row is added. For example, if most of your customers are in the US, you may want to specify that a column containing a country code be given a default value of US.

Primary Keys and Indexes

Let’s turn to the topic of primary keys and explain how that attribute relates to table indexes.

Indexes are a physical structure that can be added to any column in a database table. Indexes serve the purpose of speeding up data retrieval when that column is involved in a SQL statement. The actual data in the index is hidden, but basically the index involves a structure that maintains information on the sort order of the column, thus allowing for quicker retrieval when specific values are requested.

One downside to indexing a column is that it requires more disk storage in the database. A second negative is that indexes generally slow down data updates involving that column. This is because any time a row is inserted or modified, the index must recalculate the proper sorted order for values in that column.

Any column can be indexed, but only one column can be designated as a primary key. Specifying a column as a primary key means two things: The column will be indexed, and the column will be guaranteed to contain unique values.

As discussed in Chapter 1, primary keys accomplish two main benefits for the database user. They enable you to uniquely identify a single row in a table, and they allow you to easily relate tables to one another. And now, a third benefit can be added, namely that by being indexed, the primary key enables faster data retrieval of rows involving that column.

The main reason for having primary keys is to guarantee unique values for all rows in a table. There must always be a way of identifying single rows for updates or deletes, and the primary key ensures that this can be done.

Moreover, a primary key can actually span more than one column and can consist of two or three columns. If the primary key contains more than one column, it simply means that all those columns together will contain a unique value. This type of primary key is normally referred to as a composite primary key. As an example of when a composite primary key might be used, let’s say that you have a Movies table. You’d like to have a key that uniquely identifies each movie in the table. Rather than using a MovieID integer value as the key, you’d like to use the movie title as the key. The problem, however, is that there might exist more than one movie with the same title. To solve the problem, you might want to use two columns, the movie title and the release date, to form a composite primary to uniquely define each movie.

Because primary keys must contain unique values, they are never allowed to contain NULL values. Some value for the column must always be specified.

Finally, primary keys are often specified as auto-increment columns. By making a primary key auto-increment, database developers don’t need to worry about assigning a unique value for the column. The auto-increment feature takes care of that requirement.

Foreign Keys

In addition to primary keys, SQL databases can also designate specific columns as a foreign key. A foreign key is simply a reference from a column in one table to a column in a different table. When setting up a foreign key, you will be asked to specify both columns. The foreign key in the table being configured is often referred to as being in the child table. The referenced column in the other table is referred to as being in the parent table.

For example, let’s say you have a Customers table with a CustomerID column set up as a primary key. You also have an Orders table with an OrderID column set up as a primary key, as well as a CustomerID column. In this scenario, you can set up the CustomerID column in the Orders table as a foreign key that references the CustomerID column in the Customers table. In this situation, the Orders table is the child table and the Customers table is the parent table. The idea of the foreign key is to ensure that the CustomerID in the Orders table points to an existing customer in the Customers table, using the CustomerID column in both tables as the common element.

When a foreign key is set up, some specific actions can be specified pertaining to updates and deletes for rows in the parent table. The three most common actions are:

Image No Action

Image Cascade

Image Set Null

These three actions can be configured for either updates or deletes. Continuing with the example of the Customers and Orders tables, the most common action that might be specified is No Action. This is normally the default action if none is specified. If the CustomerID column in the Orders table is set to No Action for updates, that means that a check is made whenever an update is attempted in the parent table on the CustomerID column. If SQL tries to perform an update on the CustomerID that would result in any row in the child table pointing to a value that no longer exists, it will prevent that action from occurring. The same would be true if No Action is specified for deletes. This ensures that, when using the CustomerID column in either table, all rows in the Orders table properly point to an existing row in the Customers table.

The second alternative for a specified action for foreign keys is Cascade. This means that when a value in the parent table is updated, and that value affects rows in the child table, then SQL will automatically update all rows in the child table to reflect the new value in the parent table. Similarly, if a row in the parent table is deleted, and if that affects rows in the child table, SQL will automatically delete affected rows in the child table.

The third alternative for a specified action for foreign keys is Set Null, which is sometimes used for deletes. This means that when a value in the parent table is deleted, and if that value affects rows in the child table, SQL will automatically update all affected rows in the child table to contain a NULL value in the foreign key, indicating that a corresponding parent row no longer exists.

Creating Tables

The CREATE TABLE statement can be used to create new tables in a database. The syntax and available features vary among databases. We’ll illustrate this with a simple example that creates a table with these attributes:

Image The table name is MyTable.

Image The first column in the table is named ColumnOne and is defined as a primary key. This column will be defined as an INT (integer) datatype and also as an auto-increment column.

Image The second column in the table is named ColumnTwo and is defined as an INT datatype. This column will not allow NULL values. This column will also be defined as a foreign key, with Set Null specified for deletes, related to a column named FirstColumn in another table called RelatedTable.

Image The third column is named ColumnThree and is defined as a VARCHAR datatype with a length of 25 characters. This column will allow NULL values.

Image The fourth column is named ColumnFour, is defined as a FLOAT datatype, and will allow NULL values. It will be given a default value of 10.

Here is the CREATE TABLE statement that will create such a table in Microsoft SQL Server:

CREATE TABLE MyTable
(ColumnOne INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ColumnTwo INT NULL
REFERENCES RelatedTable (FirstColumn)
ON DELETE SET NULL,
ColumnThree VARCHAR(25) NULL,
ColumnFour FLOAT NULL DEFAULT (10))

After a table is created, an ALTER TABLE statement can be used to modify specific attributes of the table. Due to its complexity and to the vast differences between databases for this command, the syntax for the ALTER TABLE isn’t covered in this book.

As one example, the following statement could be used to modify MyTable to eliminate the ColumnThree column from the table:

ALTER TABLE MyTable
DROP COLUMN ColumnThree

The syntax for deleting an entire table is simple. To delete MyTable, issue this statement:

DROP TABLE MyTable

Creating Indexes

SQL provides a CREATE INDEX statement for creating indexes after the table is created. You can also use the ALTER TABLE statement to add or modify indexes.

To illustrate, the syntax in Microsoft SQL Server for adding a new index on ColumnFour in MyTable is:

CREATE INDEX Index2
ON MyTable(ColumnFour)

This creates a new index named Index2. To delete an index, simply issue a DROP INDEX statement such as:

DROP INDEX Index2
ON MyTable

Looking Ahead

The SQL statements for adding or modifying tables and indexes are complex but relatively unimportant to learn in detail. Database software generally provides graphical tools for modifying the structure of tables without having to resort to issuing SQL statements. The important concepts to take from this chapter are a knowledge of the various table attributes, including an understanding of how indexes and primary and foreign keys are related to each other.

In our next chapter, “Principles of Database Design,” we move from the relatively mundane task of creating tables to the much broader topic of database design. Just as tables must be created before their data is accessed, the overall structure of databases is normally designed before tables are created. So, in a sense, we’re moving in reverse through topics that are normally introduced before retrieval of data is ever attempted. The specific design of your database is, of course, an essential component of your ability to deliver quality results via SQL. If a database is poorly designed, anyone accessing data in that database will be hindered in their attempts to retrieve data. Basic knowledge of the database design principles discussed in the next chapter can go a long way toward ensuring a quality data retrieval experience.

Data Definition Language

Table Attributes

Table Columns

Primary Keys and Indexes

Foreign Keys

Creating Tables

Creating Indexes

Looking Ahead

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
35.170.81.62