cities tables from Chapter 2.
Consider the following problem: You want to make sure that no one
can insert rows in the
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
table to check if a matching record exists, and then inserting or
rejecting the new
This approach has a number of problems and is very inconvenient,
so PostgreSQL can do this for
The new declaration of the tables would look like this:
CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities(city), 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: insert or update on table "weather" violates foreign key constraint "weather_city_fkey" DETAIL: Key (city)=(Berkeley) is not present in table "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 just refer you to Chapter 5 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.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.