Working With Tables

09/06/2001

The last aboutSQL article started the discussion of Data Definition Language (DDL), the part of the SQL standard that manipulates database elements instead of the data itself. We got our feet wet by creating database containers. This week, we'll finish to job by creating tables in that database that will be ready to populate with data.

Working with tables

The DDL portion of the SQL language is not as difficult as it might be because there are a number of standard keywords that can operate on various objects in the database. In the last column, we created a database using the CREATE keyword followed by the object type and the name of our new object:

CREATE DATABASE MusicCollection

which creates a new database object named "MusicCollection."

There are a number of database objects that can be manipulated with the CREATE and DROP keywords, including:

• Databases
• Tables
• Views (which we'll discuss in a future column)
• Indexes

Databases are easy to create -- they are simply containers for other objects. But to work with table objects in the database, we need to do slightly more work. In addition to defining the table object itself, we need to defined its constituent columns, including data types and other rules for the data. The basic syntax is

CREATE TABLE table_name (
    column_name datatype [modifiers],
    (column_name datatype [modifiers],
    );

This statement creates a table with the defined columns and modifiers, if any. Modifiers can specify whether the column is required, or provide information about keys and indexing. For now, the only modifers we'll worry about are the NULL and NOT NULL modifiers. The NULL modifier indicates that the column can contain null values for any row and is the default in many database platforms. The NOT NULL modifier, on the other hand, indicates a value for the column is required. An online registration database, for example, could have the following table structure:

CREATE TABLE registrations (
    username CHAR(20) NOT NULL,
    emailAddress CHAR(80) NOT NULL,
    zipCode CHAR(10),
    age INT,
    gender CHAR(1)
    );

which would create a five-column table with two required fields and three optional fields. Note that there is no punctation until the end of the column definition. This code also demonstrates how useful whitespace can be in SQL statements: Whitespace is ignored by SQL, so there is no reason not to use it when it makes things clearer. For example, it can be used to separate each column into its own line.

Creating the MusicCollection database

The MusicCollection database we've used in previous columns provides another example of using Data Definition Language. It is a database containing two tables. We could create the entire database schema using the following DDL SQL script:

CREATE DATABASE MusicCollection;

CREATE TABLE Artists(
    ArtistID INT NOT NULL,
    ArtistName CHAR(100) NOT NULL
    );

CREATE TABLE CDs(
    CDID INT NOT NULL,
    ARTISTID INT NOT NULL,
    TITLE CHAR(40) NOT NULL,
    YEAR CHAR(4),
    RATING INT DEFAULT 0
    );

This script creates a database named "MusicCollection" and populates it with two tables, one named "Artists" and one named "CDs". The first table is simply a look-up table that links information about an artist to a particular numeric identifier. The CDs table contains information about the CD, including a required relationship to the Artists table through the ArtistID foreign key. The "Year" and "Rating" fields are optional. The Rating column also includes a default value in its definition -- zero in this case. The NULL/NOT NULL and DEFAULT value modifiers are well-implemented across most database platforms, and are fairly safe to use.

If we want to be precise, we could start the script with a DROP DATABASE MusicCollection statement to make sure we can always create a new database using this schema -- even if that database already exists. If we wanted to include the data with the schema, we could append a series of INSERT INTO statements at the end of the script to put each existing row of data into the appropriate tables.

How is your exploration of DDL going? Do you have any comments or questions you'd like to share? Post your comments Also in aboutSQL: SQL Data Types Data Definition Language Introducing SQL Sets

Most database tools can generate a script like this that contains the structure of the database and/or the data. This can be useful for moving databases from one server to another, and in many cases, even between one database platform and another. However, you may encounter compatibility problems when using a DDL SQL script from one database platform to create a database in another platform. This is because data types vary significantly between database platforms. Sometimes it's just a difference in naming structure -- such a SMALLINT as opposed to TINYINT -- which can be handled with a simple text search-and-replace. As the script gets more complex, particularly with the modifiers on each column, portability will suffer. Some database platforms have data columns without equivalents in other platforms, while the allowable modifers and combinations of modifiers can differ greatly between platforms. Be sure to check your database documentation for specifics.

Next steps

Now that we can create databases and database tables, we'll need to know how to change the tables we've created. In the next column, we'll take a quick look at data types for various database platforms -- one of the primary "gotchas" when moving database schemas from one platform to another. Until then, feel free to contact me with comments and questions.

John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.

Read more aboutSQL columns.

---

Return to ONLamp.com.