SQL Table Commands Overview

As mentioned previously in this series of SQL articles, databases are primarily composed of tables. The “columns and rows” structure of the table allows data to be efficiently inserted, manipulated, updated, and deleted from the database. The three most important commands used to work with tables, CREATE TABLE, ALTER TABLE, and DROP TABLE, are covered in this article.

Please note the syntax used in this article is for PostgreSQL. Other SQL databases may require slightly different syntax to perform the same operations.

Building Upon an Empty Table

The CREATE TABLE command essentially defines a table’s existence and how it can be used in future operations. We will start by examining the simplest CREATE TABLE command, shown in Figure 1.

This command creates a table called foo that has no columns and no constraints. While this may seem like a completely useless exercise, it gives us a point from which we can explore other parts of tables. Let’s say this table will contain some statistics from today’s Pittsburgh Steelers football game against the Cleveland Browns. Today is Sunday, November 11, 2007, and the Steelers played rather an odd game against the Browns that produced some unusual numbers. The first thing we need to do, then, is rename the table to something more appropriate, like football, as shown in Figure 2.

The two quarterbacks in the game were Ben Roethlisberger (Steelers) and Derek Anderson (Browns). Quarterback statistics primarily involve passing the football, so let’s add six columns to the table: name, passes_completed, passes_attempted, yards, touchdowns, and interceptions.

Figure 3 shows the ALTER TABLE command used to add six columns and the PostgreSQL d command used to verify the addition of the columns. The name column is of the data type TEXT, while the other columns are of the data type INTEGER. SQL data types are covered in another article in this series.

Using the INSERT INTO command, appropriate values for the two quarterbacks are added to the table. This command is not shown here. Now that the table contains some data, we need to ensure future data has some integrity when it is inserted into the table later. The most basic way of doing this is to place NOT NULL constraints on the columns. A NOT NULL constraint lets the database know a legal value must appear for that column in every row, even if that value is zero.

One of the purposes of constraining a column this way is to ensure programming errors in database applications do not leave empty fields in the database. If this happens in a database application that handles errors well, the user will be notified of the problem when the application is unable to insert data into the database and can notify the developer of the bug in the program.

The addition of these constraints is shown and verified in Figure 4.

Suppose we would later add statistics to this table that did not have anything to do with quarterbacks. Perhaps we could include Jeff Reed’s fantastic kicking game, which had nothing to do with throwing the football. In that case, fields like passes_completed and passes_attempted would be useless and might be assigned the NULL value. However, it is just as easy to assign a value of zero, and data integrity would still be preserved through the NOT NULL modifiers on the columns.

Databases are much more efficient at finding records if they have some sort of unique identifier, like an “ID” number. In our example, if each football player has a specific ID, then the searching algorithm does not have try as hard to find a player. For a computer, searching a list of easy-to-use numbers is much simpler and requires much less time than searching a long list of names.

In order to make this table efficiently searchable, we should give each player a unique ID number. We will add a column called id with the data type SERIAL and make it the primary key for the table, as shown in Figure 5. While keys are very important to database development, this particular exercise is not concerned with the KEY part of the column as much as the SERIAL part of the column. The SERIAL data type ensures the values of id are unique and auto-incrementing throughout the entire table, even when new data is inserted. SERIAL columns are also automatically constrained as NOT NULL. Keys are covered in depth in another article in this series.

The ALTER TABLE command in Figure 5 created a new column called id in the football table. But, as you can see, that’s not all it did.

Next Page: SQL Serial Data Type and Complex Table