Postgresql 中文操作指南

3.6. Inheritance #

继承是面向对象数据库中的一个概念。它开辟了数据库设计中的有趣的新可能性。

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

让我们创建两个表：一张表 cities 和一张表 capitals。自然，首都也是城市，所以您希望当列出所有城市时，以某种方式显示首都。如果您真的聪明，您可能会发明这样的方案：

Let’s create two tables: A table cities and a table capitals. Naturally, capitals are also cities, so you want some way to show the capitals implicitly when you list all cities. If you’re really clever you might invent some scheme like this:

CREATE TABLE capitals (
  name       text,
  population real,
  elevation  int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE VIEW cities AS
  SELECT name, population, elevation FROM capitals
    UNION
  SELECT name, population, elevation FROM non_capitals;

这在查询方面很好用，但当您需要更新几行时，情况会变得很糟糕。

This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

一个更好的解决方案是：

A better solution is this:

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

在这种情况下，capitals 的一行 inherits 其 parent 的所有列（name、population 和 elevation ）。列 name 的类型是 text，一种变长字符字符串的原生 PostgreSQL 类型。capitals 表具有一个额外的列 state，它显示其状态缩写。在 PostgreSQL 中，一张表可以从零个或多个其他表继承。

In this case, a row of capitals inherits all columns (name, population, and elevation) from its parent, cities. The type of the column name is text, a native PostgreSQL type for variable length character strings. The capitals table has an additional column, state, which shows its state abbreviation. In PostgreSQL, a table can inherit from zero or more other tables.

例如，以下查询将找到位于高过 500 英尺高度的所有城市的名称，包括州首府：

For example, the following query finds the names of all cities, including state capitals, that are located at an elevation over 500 feet:

SELECT name, elevation
  FROM cities
  WHERE elevation > 500;

返回：

which returns:

   name    | elevation
-----------+-----------
 Las Vegas |      2174
 Mariposa  |      1953
 Madison   |       845
(3 rows)

另一方面，以下查询将找到所有不是州首府且位于高过 500 英尺高度的城市：

On the other hand, the following query finds all the cities that are not state capitals and are situated at an elevation over 500 feet:

SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;
   name    | elevation
-----------+-----------
 Las Vegas |      2174
 Mariposa  |      1953
(2 rows)

这里的 ONLY 位于 cities 之前，表明只应针对 cities 表运行查询，而不是位于继承层次结构中 cities 以下的表。我们已经讨论过的许多命令——SELECT、UPDATE 和 DELETE——都支持此 ONLY 标记。

Here the ONLY before cities indicates that the query should be run over only the cities table, and not tables below cities in the inheritance hierarchy. Many of the commands that we have already discussed — SELECT, UPDATE, and DELETE — support this ONLY notation.

Note

尽管继承经常是有用的，但它尚未与唯一约束或外键集成，限制了它的有用性。有关更多详细信息，请参见 Section 5.10。

Although inheritance is frequently useful, it has not been integrated with unique constraints or foreign keys, which limits its usefulness. See Section 5.10 for more detail.