Tables, Constraints, and Indexes - PostgreSQL: Up and Running - page 111

Example 6-5. Building foreign key constraints and covering indexes
set search_path = census , public ;
ALTER TABLE facts ADD CONSTRAINT fk_facts_1 FOREIGN KEY ( fact_type_id )
REFERENCES lu_fact_types ( fact_type_id )
ON UPDATE CASCADE ON DELETE RESTRICT ;
CREATE INDEX fki_facts_1 ON facts ( fact_type_id );
We define a foreign key relationship between our facts and fact_types tables.
This prevents us from introducing fact types into facts unless they are already
present in the fact types lookup table.
We add a cascade rule that automatically updates the fact_type_id in our facts
table should we renumber our fact types. We restrict deletes from our lookup
table so fact types in use cannot be removed. RESTRICT is the default behavior,
but we suggest stating it for clarity.
Unlike for primary key and unique constraints, PostgreSQL doesn't
automatically create an index for foreign key constraints; you should add this
yourself to speed up queries.
Unique Constraints
Each table can have no more than a single primary key. If you need to enforce uniqueness
on other columns, you must resort to unique constraints or unique indexes. Adding a
unique constraint automatically creates an associated unique index. Similar to primary
keys, unique key constraints can participate in REFERENCES part of foreign key con‐
straints and cannot have NULL values. A unique index without a unique key constraint
does allow NULL values. The following example shows how to add a unique index:
ALTER TABLE logs_2011 ADD CONSTRAINT uq UNIQUE ( user_name , log_ts );
Often you'll find yourself needing to ensure uniqueness for only a subset of your rows.
PostgreSQL does not offer conditional unique constraints, but you can achieve the same
effect by using a partial uniqueness index. See “Partial Indexes” on page 116 .
Check Constraints
Check constraints are conditions that must be met for a field or a set of fields for each
row. The query planner can also take advantage of check constraints and abandon
queries that don't meet the check constraint outright. We saw an example of a check
constraint in Example 6-2 . That particular example prevents the planner from having
to scan rows failing to satisfy the date range specified in a query. You can exercise some
creativity in your check constraints, because you can use functions and Boolean ex‐
pressions to build complicated matching conditions. For example, the following con‐
straint requires all user names in the logs tables to be lowercase:
ALTER TABLE logs ADD CONSTRAINT chk CHECK ( user_name = lower ( user_name ));