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.

{qbapagebreak title=SQL Serial Data Type and Complex Table}

SQL Serial Data Type and Complex Table

When the database saw the SERIAL data type, it automatically created a sequence called football_id_seq. This sequence is visible in the database’s list of relations (i.e. the d command in PostgreSQL) and is an automatically incrementing value. When a new record is added to the football table, it will receive the next id value available. Let’s have a look at how this works in Figure 6.

Figure 6 shows the id values that were automatically assigned to Ben Roethlisberger and Derek Anderson when the id column was added in Figure 5. Then, we used INSERT INTO to add another record for Jeff Reed, Pittsburgh’s kicker. The important thing to note here is that no id value was specified. Instead, it was added automatically as the next available integer from the football_id_seq sequence. In this case, the next available integer was 3, since 1 and 2 were assigned to Ben Roethlisberger and Derek Anderson.

At this point, we have explored some of the more common parts of tables. We created a table named foo, renamed it to football, added related columns, constrained those columns with the NOT NULL constraint, added an id field with the SERIAL data type, and then saw how SERIAL fields can be automatically incremented and assigned when records are added to a table.

We could have done all of this with one CREATE TABLE command, and the next section will show how that is possible. For now, let’s DROP TABLE, so we can start over with a clean slate. This is shown in Figure 7. The DROP TABLE command also drops associated sequences and indexes.

Using CREATE TABLE to Create a Complex Table

Figure 8 shows how to use CREATE TABLE to incorporate all the changes we made to the foo table in the previous section.

As you can see in Figure 9, the new football table is now configured exactly as the old football table was.

Now that the football table is set up again, the only thing remaining to do is add the data again. This exercise is left to the reader!