Database Reference
In-Depth Information
CHAPTER 6
Tables, Constraints, and Indexes
Tables form the building block of relational-database storage. Structuring tables so that
they form meaningful relationships is the key to relational-database design. In Post‐
greSQL, constraints enforce relationships between tables. To distinguish a table from
just a heap of data, we establish indexes. Much like the indexes you find at the end of
topics or the tenant list at the entrances to grand office buildings, indexes point to
locations in the table so you don't have to scour the table from top to bottom every time
you're looking for something.
In this chapter, we introduce syntax for creating tables and adding rows. We then move
on to constraints to ensure that your data doesn't get out of line. Finally, we show you
how to add indexes to your tables to expedite search. Indexing a table is as much a
programming task as it is an experimental endeavor. A misappropriated index is worse
than useless. Not all indexes are created equal. Algorithmists have devised different
kinds of indexes for different data types, all in the attempt to scrape that last zest of speed
from a query.
Tables
In addition to ordinary data tables, PostgreSQL offers several kinds of tables that are
rather uncommon: temporary, unlogged, inherited, typed, and foreign (covered in
Chapter 10
).
Basic Table Creation
Example 6-1
shows the table creation syntax, which is similar to what you'll find in all
SQL databases.
