Recall the weather and
cities tables from Chapter 22. Consider the following problem: You
want to make sure that no one can insert rows in the
weather table that do not have a matching
entry in the cities table. This is called
maintaining the referential integrity of
your data. In simplistic database systems this would be
implemented (if at all) by first looking at the
cities table to check if a matching record
exists, and then inserting or rejecting the new
weather records. This approach is very inconvenient, so
PostgreSQL can do everything for you
if you specify primary and foreign key constraints in your table declarations:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities,
temp_lo int,
temp_hi int,
prcp real,
date date
);
|
Now try inserting an invalid record:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28'); |
ERROR: <unnamed> referential integrity violation - key referenced
from weather not found in cities |
The behavior of foreign keys can be finely tuned to your
application. We will not go beyond this simple example in this
tutorial, but refer you to the Red Hat Database SQL Guide and Reference for more information.
Making correct use of
foreign keys will definitely improve the quality of your database
applications, so you are strongly encouraged to learn about them.