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.
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
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
DROP keywords, including:
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
NOT NULL modifiers. The
NULL modifier indicates that the
column can contain null values for any row and is the default in many database
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,
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.
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,
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
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
How is your exploration of DDL going? Do you have any comments or questions you'd like to share?
Also in aboutSQL:
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.
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.
Copyright © 2009 O'Reilly Media, Inc.