Postgresql 中文操作指南

3.3. Foreign Keys #

回想从 weather 和 cities 表格中取出的 Chapter 2 表格。考虑以下问题：你想要确保任何人不能在 weather 表格中插入没有任何数据与 cities 表格匹配的行。这称为保持数据 referential integrity 性。在简单数据库系统中，这应通过首先查看 cities 表格以检查是否存在匹配记录，然后插入或拒绝新 weather 记录来实现（如果需要）。此方法遇到很多问题，并且非常不方便，所以 PostgreSQL 可以帮你进行该操作。

Recall the weather and cities tables from Chapter 2. 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 has a number of problems and is very inconvenient, so PostgreSQL can do this for you.

表的新的声明将如下所示：

The new declaration of the tables would look like this:

CREATE TABLE cities (
        name     varchar(80) primary key,
        location point
);

CREATE TABLE weather (
        city      varchar(80) references cities(name),
        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".

外键的行为可以针对你的应用程序进行精细调整。我们不会在本教程中超越这个简单示例，但只将你转到 Chapter 5 以了解更多信息。正确使用外键无疑会提高你的数据库应用程序的质量，所以强烈建议你学习它们。

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.