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!