Database Reference
In-Depth Information
Example 6-4. Using TYPE to define new table structure
CREATE
TABLE
super_users
OF
basic_user
(
CONSTRAINT
pk_su
PRIMARY
KEY
(
user_name
));
When creating tables from data types, you can't alter the columns of the table. Instead,
add or remove columns to the composite data type, and PostgreSQL will automatically
propagate the changes to the table structure. Much like inheritance, the advantage of
this approach is that if you have many tables sharing the same underlying structure and
you need to make a universal alteration, you can do so by simply changing the under‐
lying composite type.
Let's say we now need to add a phone number to our
super_users
table from
Example 6-4
. All we have to do is execute the following command to alter the underlying
type:
ALTER
TYPE
basic_user
ADD
ATTRIBUTE
phone
varchar
(
10
)
CASCADE
;
Normally, you can't change the definition of a type if tables depend on that type. The
CASCADE
modifier overrides this restriction, applying the same change to all the depen‐
dent tables.
Constraints
PostgreSQL constraints are the most advanced (and most complex) of any database
we've worked with. Not only do you create constraints, but you can also control all facets
of how a constraint handles existing data, any cascade options, how to perform the
matching, which indexes to incorporate, conditions under which the constraint can be
violated, and more. On top of it all, you can pick your own name for each constraint.
For the full treatment, we suggest you review the
official documentation
. You'll find
comfort in knowing that taking the default settings usually works out fine. We'll start
off with something familiar to most relational folks: foreign key, unique, and check
constraints. Then we'll move on to exclusion constraints, introduced in version 9.0.
Names of primary key and unique key constraints must be unique
within a given schema. The general practice is to include the name
of the table and column as part of the name of the key. For the sake
of brevity, our examples might not abide by this general practice.
Foreign Key Constraints
PostgreSQL follows the same convention as most databases that support referential
integrity. You can specify cascade update and delete rules to avoid pesky orphaned re‐
cords. We show you how to add foreign key constraints in
Example 6-5
.
