Tables are quite possibly the most important aspect of SQL to understand inside and out, as all of your data will reside within them. In order to be able to correctly plan and design your SQL data structures, and any programmatic routines toward accessing and applying that data, a thorough understanding of tables is an absolute pre-requisite.

From a general perspective, the columns of a table can be considered to describe the type and significance of a given field (an intersection of a column and row), while conversely, each row represents a complete record whose fields are described from left to right by the corresponding column's name and type. Each field in a row is implicitly correlated with each other field in that row. In this sense, columns can be thought of as descriptors for the discrete, sequential elements of a row, and each row can be thought of as a stored record matching that description.

Table 3-1. An Example SQL Table

id

title

author_id

subject_id

7808

The Shining

4156

9

156

The Tell-Tale Heart

15

9

4513

Dune

1866

15

4267

2001: A Space Odyssey

2001

15

1608

The Cat in the Hat

1809

2

1590

Bartholomew and the Oobleck

1809

2

Table 3-1 illustrates a simple table called books , used by our imaginary bookstore, Book Town . This table is frequently referred to in this book throughout our examples. Each of its stored records describes a book by a numeric identifier, title, author identifier, and subject identifier. These characteristics, from left to right, are described by its columns ( id , title , author_id , and subject_id ).

As you can see, this describes a table with four columns, in a fixed left to right order, currently populated by six rows (also known as tuples, or records). It is essential to understand that in a relational database, while a table has a fixed column order, rows themselves are inherently unordered . You will see later, as the query structure is further explained in Chapter 4 , that there are ways within SQL to order selected rows. However, the rows in the database themselves are not automatically ordered in any consistently predictable way. The explicit ordering of records must be an important consideration in the creation of appropriate and applicable SQL queries in situations when the order used is meaningful.

Every table must have at least one column, but tables may at times contain no rows. This is because each vertical column corresponds to a relatively fixed attribute of the data represented in that table (such as the title column in the previous example's books table). Without a column, a row's contents would be ambiguous; without a row, a table is merely lacking recorded data. As of PostgreSQL 7.1, there is a maximum of 1600 columns to a table, and an unlimited number of rows (i.e., limited only by hardware limitations, such as disk space).

In Table 3-1 , the column names fairly clearly indicate the significance of each column. The decision of how to name these is fairly arbitrary, though, and care must be taken in planning table names and conventions to avoid ambiguity.

Though it may not be immediately obvious, each of the columns of a table have an associated data type . While a column's data type helps to further describe the sort of information it contains, it constrains the kind of data that may be inserted into the column. For example, the author_id column is of type integer ; this signifies that any insertion attempts not consisting of pure a integer (e.g., "110a") will fail. These types are described in more detail in the section called Data Types .

This section introduced the general concepts of how data is logically arranged in a relational database, and within tables. The next section, the section called SQL Statements , addresses the topic of SQL Statements , which are the basis for all interactions with the database.