Postgres-XL 10 使用介绍（二）

原创

已于 2022-06-20 21:33:11 修改 · 650 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #database

于 2022-06-19 10:48:41 首次发布

本文探讨了SQL中的约束类型，如检查约束确保数据符合业务规则，非空约束避免空值，以及如何利用模式组织数据库对象，包括创建、权限管理和搜索路径的设定。

#### 5.3 Constraints

数据类型是一种限制可以存储在表中的数据类型的方法。但是，对于许多应用程序，它们提供的约束过于粗糙。例如，包含产品价格的列可能只应接受正值。但是没有标准的数据类型只接受正数。另一个问题是，您可能希望相对于其他列或行约束列数据。例如，在包含产品信息的表中，每个产品编号应只有一行

为此，SQL允许您定义列和表的约束。约束使您可以根据需要尽可能多地控制表中的数据。如果用户尝试将数据存储在违反约束的列中，则会引发错误。即使该值来自默认值定义，也是如此。

##### 5.3.1 Check Constraints

检查约束是最通用的约束类型。它允许您指定某个列中的值必须满足布尔值（真值）表达式。例如，要使产品价格为正数，可以使用：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
```

如您所见，约束定义位于数据类型之后，就像默认值定义一样。默认值和约束可以任何顺序列出。检查约束由关键字CHECK以及括号中的表达式组成。检查约束表达式应包含受约束的列，否则约束不会有太大意义。

您还可以为约束指定一个单独的名称。这样可以澄清错误消息，并允许您在需要更改约束时引用约束。语法为：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
```

因此，要指定命名约束，请使用关键字CONSTRAINT，后跟标识符和约束定义。（如果您未以这种方式指定约束名称，则系统会为您选择一个名称。）

检查约束也可以引用几列。假设您存储正常价格和折扣价格，并且要确保折扣价格低于正常价格：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
```

前两个约束应该看起来很熟悉。第三个使用新语法。它没有附加到特定的列，而是在逗号分隔的列列表中显示为单独的项目。列定义和这些约束定义可以以混合顺序列出。

我们说前两个约束是列约束，而第三个约束是表约束，因为它是与任何一个列定义分开编写的。列约束也可以写为表约束，而反向约束不一定是可能的，因为列约束应该仅指代其所附的列。（PostgreSQL并不强制执行该规则，但是如果您希望表定义与其他数据库系统一起使用，则应遵循该规则。）上面的示例也可以写成：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
```

或者

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0 AND price > discounted_price)
);
```

可以使用与列约束相同的方式将名称分配给表约束：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
```

应当注意，如果校验表达式的值为真或空值，则满足校验约束。由于大多数表达式在任何操作数为null的情况下都会求值为null，因此它们不会在受约束的列中阻止null值。为了确保一列不包含空值，可以使用下一节中描述的非空约束。

##### 5.3.2 Not-Null Constraints

非空约束仅指定列不得采用空值。语法示例：

```sql
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
```

非空约束始终被写为列约束。非空约束在功能上等同于创建检查约束CHECK（column_name IS NOT NULL），但是在PostgreSQL中，创建显式非空约束更为有效。缺点是您不能为以此方式创建的非空约束指定显式名称。

当然，一列可以有多个约束。只需一个接一个地写约束：

```sql
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
```

顺序无关紧要。它不一定确定检查约束的顺序。

NOT NULL约束具有相反的含义：NULL约束。这并不意味着该列必须为空，这肯定是无用的。相反，这只是选择默认行为，即列可能为空。 NULL约束在SQL标准中不存在，因此不应在可移植应用程序中使用。（它被添加到PostgreSQL是为了与其他一些数据库系统兼容。）但是，某些用户喜欢它，因为它使切换脚本文件中的约束变得容易。例如，您可以从以下内容开始：

```sql
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);
```

然后在需要的地方插入NOT关键字。在大多数数据库设计中，大多数列应标记为非null

##### 5.3.3 Unique Constraints

唯一性约束可确保一列或一组列中包含的数据在表的所有行中都是唯一的。语法为：

```sql
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
```

作为列约束编写时，以及：

```sql
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
```

作为表约束编写时。要为一组列定义唯一约束，请将其写为表约束，并以逗号分隔列名：

```sql
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
```

这指定指示列中的值组合在整个表中是唯一的，尽管任何一列都不必（通常不是）唯一。您可以按照通常的方式为唯一的约束分配自己的名称：

```sql
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
```

添加唯一约束将在约束中列出的列或一组列上自动创建唯一的B树索引。不能将仅覆盖某些行的唯一性限制写为唯一性约束，但是可以通过创建唯一的部分索引来实施这种限制。

通常，如果表中有多于一行的行，其中约束中包含的所有列的值均相等，则违反唯一约束。但是，在此比较中，永远不会将两个空值视为相等。这意味着即使在存在唯一约束的情况下，也可以在至少一个约束列中存储包含空值的重复行。此行为符合SQL标准，但是我们听说其他SQL数据库可能不遵循此规则。因此，在开发可移植的应用程序时要小心。

在Postgres-XL中，在分布式表中，唯一约束必须包含表的分布列。这是因为Postgres-XL当前仅允许其向下推送到Datanodes以便在本地实施。如果我们将分发列包含在唯一约束中，则可以在本地强制实施。如果表是由ROUNDROBIN分发的，则我们不能强制执行UNIQUE约束，因为它没有分发列。一个列的相同值可能存在于多个节点上。复制表中的UNIQUE约束没有任何限制。当对UNIQUE约束使用表达式时，该表达式必须包含其父表的distribution列。它也不能使用其他列。

##### 5.3.4 Primary Keys

主键约束指示一列或一组列可以用作表中行的唯一标识符。这要求这些值必须是唯一的并且不能为null。因此，以下两个表定义接受相同的数据：

```sql
CREATE TABLE products (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
```

主键可以跨越多列；语法类似于唯一约束：

```sql
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
```

添加主键将自动在主键中列出的列或一组列上创建唯一的B树索引，并强制将这些列标记为NOT NULL。

一个表最多可以有一个主键。（可以有任意数量的唯一约束和非空约束，它们在功能上几乎是同一件事，但是只能将其中一个标识为主键。）关系数据库理论规定，每个表都必须具有一个主键。 PostgreSQL没有强制执行此规则，但是通常最好遵循它。

正如讨论UNIQUE约束时提到的那样，分发列必须包含在PRIMARY KEY中。其他限制也适用于主键。当对PRIMARY KEY约束使用表达式时，该表达式必须包含其父表的distribution列。它也不能使用其他列。

主键对于文档目的和客户端应用程序都是有用的。例如，允许修改行值的GUI应用程序可能需要知道表的主键才能唯一地标识行。如果已经声明了主键，则数据库系统还可以通过多种方式使用主键。例如，主键定义了引用其表的外键的默认目标列。

##### 5.3.5 Foreign Keys

外键约束指定一列（或一组列）中的值必须与另一个表的某一行中出现的值匹配。我们说这保持了两个相关表之间的参照完整性。

假设您拥有我们已经使用过几次的产品表：

```sql
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
```

我们还假设您有一个表来存储这些产品的订单。我们要确保订单表仅包含实际存在的产品的订单。因此，我们在引用产品表的订单表中定义了一个外键约束。

```sql
CREATE TABLE orders (
order_id integer,
product_no integer REFERENCES products (product_no),
quantity integer
) DISTRIBUTE BY HASH(product_no);
```

请注意，带有REFERENCES的列应该是分布列。在这种情况下，我们不能将PRIMARY KEY添加到order_id，因为PRIMARY KEY也必须是分发列。引入此限制是因为约束仅在每个Datanode中本地执行。

现在，无法使用没有出现在产品表中的非NULL product_no条目创建订单。

我们说在这种情况下，订单表是引用表，产品表是引用表。同样，也有引用和引用列。

您也可以将以上命令缩短为：

```sql
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
```

因为在没有列列表的情况下，被引用表的主键用作被引用列。在Postgres-XL中，您不能在REFERENCES子句中省略列名。外键也可以约束和引用一组列。通常，它需要以表约束形式编写。这是一个人为的语法示例：

```sql
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
```

当然，约束列的数量和类型需要与引用列的数量和类型匹配。在Postgres-XL中，不能为不同的列同时指定PRIMARY KEY和REFERENCES键。

您可以按照通常的方式为外键约束分配自己的名称。

一个表可以具有多个外键约束。这用于实现表之间的多对多关系。假设您有关于产品和订单的表格，但是现在您希望允许一个订单包含可能很多产品（以上结构不允许）。您可以使用此表结构：

```sql
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);

CREATE TABLE order_items (
product_no integer REFERENCES products,
order_id integer REFERENCES orders,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
```

请注意，主键与最后一个表中的外键重叠。在Postgres-EXCEL中，不能指定多个外键约束。

我们知道，外键不允许创建与任何产品都不相关的订单。但是，如果在创建引用该产品的订单后将其删除，该怎么办？ SQL也允许您处理该问题。直观地，我们有几种选择：

- 禁止删除引用的产品
- 删除订单
- 还有吗

为了说明这一点，让我们在上面的多对多关系示例中实施以下策略：当某人想要删除仍由订单引用的产品（通过order_items）时，我们将其禁止。如果有人删除了订单，则订单项也将被删除：

```sql
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);

CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);

CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
)
```

限制删除和级联删除是两个最常见的选项。 RESTRICT防止删除引用的行。 NO ACTION表示如果在检查约束时仍存在任何引用行，则会引发错误；如果您未指定任何内容，则这是默认行为。（这两种选择之间的本质区别是，NO ACTION允许将支票推迟到事务的后面，而RESTRICT不允许。）CASCADE指定在删除引用的行时，应自动删除引用该行的行也一样还有其他两个选项：SET NULL和SET DEFAULT。当删除引用的行时，这将导致引用行中的引用列分别设置为null或它们的默认值。请注意，这些不能使您免于遵守任何约束条件。例如，如果某个操作指定SET DEFAULT，但默认值不满足外键约束，则该操作将失败。

类似于ON DELETE，当更改（更新）引用的列时，也会调用ON UPDATE。可能的动作是相同的。在这种情况下，CASCADE意味着应将引用列的更新值复制到引用行中。

通常，如果引用行的任何引用列为null，则不必满足外键约束。如果将MATCH FULL添加到外键声明中，则仅当其所有引用列都为null时，引用行才能满足约束条件（因此，确保null和非null值的混合可确保不通过MATCH FULL约束）。如果您不希望引用行能够避免满足外键约束，则将引用列声明为NOT NULL。

外键必须引用作为主键或形成唯一约束的列。这意味着被引用的列始终具有一个索引（作为主键或唯一约束的基础的索引）；因此，检查引用行是否有匹配项将非常有效。由于从被引用表中删除一行或对被引用列进行UPDATE将需要对引用表进行扫描以查找与旧值匹配的行，因此通常也建议对引用列进行索引。因为这并不总是需要的，并且在如何建立索引方面有很多选择，所以外键约束的声明不会自动在引用列上创建索引。

有关更新和删除数据的更多信息，请参见第6章。另请参见参考文档中CREATE TABLE的外键约束语法说明。

##### 5.3.6 Exclusion Constraints

排除约束条件确保，如果使用指定的运算符在指定的列或表达式上比较任何两行，则这些运算符比较中的至少一个将返回false或null。语法为：

```sql
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH &&)
);
```

有关详细信息，另请参见CREATE TABLE ... CONSTRAINT ... EXCLUDE。

添加排除约束将自动创建约束声明中指定类型的索引。**Postgres-XL不支持排除约束。**

#### 5.4 System Columns

每个表都有几个系统隐式定义的系统列。因此，这些名称不能用作用户定义列的名称。（请注意，这些限制与名称是否是关键字是分开的；引用名称将使您无法逃避这些限制。）您实际上并不需要担心这些列；您不必担心这些限制。只知道它们存在。

oid

行的对象标识符（对象ID）。仅当表是使用WITH OIDS创建的，或者当时已设置default_with_oids配置变量时，才显示此列。此列是oid类型（与列名称相同）；有关类型的更多信息，请参见第8.18节。

请注意，Postgres-XL不会在群集之间强制执行OID完整性。 OID在每个协调器和数据节点中本地分配。您可以在表达式中使用它，但不要期望整个XL群集的OID值都相同。

tableoid

包含该行的表的OID。对于从继承层次结构中选择的查询，此列特别方便（请参见5.9节），因为如果没有它，则很难确定一行来自哪个表。可以将tableoid与pg_class的oid列连接起来以获得表名。

请注意，Postgres-XL不会在群集之间强制执行OID完整性。 OID在每个协调器和数据节点中本地分配。您可以在表达式中使用它，但不要期望整个XL群集中的OID值都相同。

xmin

此行版本的插入事务的标识（事务ID）。（行版本是行的单独状态；行的每次更新都会为同一逻辑行创建一个新的行版本。）

cmin

插入事务中的命令标识符（从零开始）。

xmax

删除事务的标识（事务ID），对于未删除的行版本为零。在可见行版本中，此列可能为非零。这通常表明删除事务尚未提交，或者尝试删除的操作已回滚。

cmax

删除事务中的命令标识符，或者为零。

ctid

行版本在其表中的物理位置。请注意，尽管可以使用ctid快速定位行版本，但是如果通过VACUUM FULL更新或移动了行的ctid，则该行的ctid将会更改。因此，ctid不能用作长期行标识符。应该使用OID或更好的用户定义序列号来标识逻辑行。

在Postgres-XL中，ctid对于协调器和数据节点是本地的。在SQL语句中使用此值不是一个好习惯，并且在使用它更新数据时可能非常危险。

OID为32位，并从单个群集范围的计数器分配。在大型或长期存在的数据库中，计数器可能会回绕。因此，除非您采取措施确保确实如此，否则认为OID是唯一的是不好的做法。如果需要识别表中的行，强烈建议使用序列生成器。但是，只要采取一些其他预防措施，也可以使用OID：

- 应该在每个表的OID列上创建一个唯一约束，将使用该OID来标识行。当存在这样的唯一约束（或唯一索引）时，系统注意不要生成与现有行匹配的OID。（当然，只有在表包含少于232（40亿）行的情况下才有可能，并且实际上表的大小最好远小于该行，否则性能可能会受到影响。）
- OID绝对不应在表之间是唯一的；如果需要数据库范围的标识符，请使用tableoid和行OID的组合。
- 当然，有问题的表必须使用OIDS创建。从PostgreSQL 8.1开始，缺省为WITHOUT OIDS。

事务标识符也是32位的数量。在寿命长的数据库中，事务ID可能会回绕。如果采取适当的维护程序，这不是致命的问题；有关详细信息，请参见第24章。但是，长期依赖事务ID（超过十亿笔交易）的唯一性是不明智的。

命令标识符也是32位量。这在单个事务中创建了232（40亿）条SQL命令的硬限制。实际上，此限制不是问题-请注意，此限制是针对SQL命令的数目，而不是处理的行数。同样，只有实际修改数据库内容的命令才会使用命令标识符。

#### 5.5 Modifying Tables

当您创建表并意识到自己犯了一个错误或应用程序的要求发生变化时，可以删除该表并重新创建它。但这不是一个方便的选择，如果该表已被数据填充，或者该表已被其他数据库对象引用（例如，外键约束）。因此，PostgreSQL提供了一系列命令来修改现有表。请注意，从概念上讲，这与更改表中包含的数据不同：在这里，我们有兴趣更改表的定义或结构。

你可以做如下操作:

- 添加列
- 删除列
- 添加约束
- 删除约束
- 修改默认值
- 修改数据类型
- 修改列名
- 修改表名

所有这些操作都是使用ALTER TABLE命令执行的，该命令的参考页包含此处未提供的详细信息。

除了这些操作之外，在Postgres-XL中，您还可以使用ALTER TABLE命令执行以下操作：

- 修改表的分布式策略
- 添加节点到已有的分布式节点中
- 从已有的分布式节点中移除节点
- 如果现有值在新数据类型的范围内，则修改分布列定义

在Postgres-XL中，不允许以下操作：

- 修改分布列值
- 删除分布式字段

##### 5.5.1 Adding a Column

给表添加一个列,可以使用如下命令:

```sql
ALTER TABLE products ADD COLUMN description text;
```

最初，新列将填充给定的默认值（如果未指定DEFAULT子句，则为null）。

您还可以使用通常的语法同时在列上定义约束：

```sql
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
```

实际上，可以在此处使用可以应用于CREATE TABLE中的列描述的所有选项。但是请记住，默认值必须满足给定的约束，否则ADD将失败。另外，您可以在正确填写新列之后再添加约束（见下文）。

添加具有默认值的列需要更新表的每一行（以存储新的列值）。但是，如果未指定默认值，则PostgreSQL可以避免物理更新。因此，如果您打算用非默认值填充该列，则最好添加无默认值的列，使用UPDATE插入正确的值，然后添加任何所需的默认值，如下所述。

##### 5.5.2 Removing a Column

要删除列，请使用类似以下的命令：

```sql
ALTER TABLE products DROP COLUMN description;
```

列中的所有数据都会消失。涉及该列的表约束也被删除。但是，如果该列被另一个表的外键约束引用，则PostgreSQL将不会默默地删除该约束。您可以通过添加CASCADE授权删除所有依赖于该列的内容：

```sql
ALTER TABLE products DROP COLUMN description CASCADE;
```

请参阅第5.13节，以获取有关其背后的一般机制的描述。

##### 5.5.3 Adding a Constraint

要添加约束，请使用表约束语法。例如：

```sql
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
```

要添加不能写为表约束的非空约束，请使用以下语法：

```sql
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
```

约束将被立即检查，因此表数据必须满足约束才能添加。

请记住，分布式列必须包含在UNIQUE和REFERENCE约束中。

##### 5.5.4 Removing a Constraint

要删除约束，您需要知道其名称。如果您给它起个名字，那很容易。否则，系统会分配一个生成的名称，您需要找出该名称。 psql命令\ d表名在这里可能会有所帮助；其他接口也可能提供检查表详细信息的方法。然后命令是：

```sql
ALTER TABLE products DROP CONSTRAINT some_name;
```

（如果要处理生成的约束名称，例如$ 2，请不要忘记将其双引号使之成为有效的标识符。）

与删除列一样，如果要删除其他依赖项的约束，则需要添加CASCADE。一个示例是外键约束取决于引用列上的唯一键或主键约束。

除非空约束以外，所有约束类型的工作原理都相同。要删除非空约束，请使用：

```sql
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
```

（请记住，非空约束没有名称。）

##### 5.5.5 Changing a Column's Default Value

要为列设置新的默认值，请使用以下命令：

```sql
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
```

请注意，这不会影响表中的任何现有行，而只是更改以后的INSERT命令的默认值。

要删除任何默认值，请使用：

```sql
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
```

这实际上与将默认值设置为null相同。因此，在未定义默认值的地方删除默认值不是错误，因为默认值隐式为空值。

##### 5.5.6 Changing a Column's Data Type

要将列转换为其他数据类型，请使用类似以下命令：

```sql
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
```

仅当可以通过隐式强制转换将列中的每个现有条目转换为新类型时，此操作才会成功。如果需要更复杂的转换，则可以添加USING子句，该子句指定如何从旧值计算新值。

PostgreSQL将尝试将列的默认值（如果有）转换为新类型，以及涉及该列的所有约束。但是这些转换可能会失败，或者会产生令人惊讶的结果。通常最好在更改其类型之前先在列上放下任何约束，然后再添加适当修改后的约束。

##### 5.5.7 Renaming a Column

给列重命名,使用如下命令:

```sql
ALTER TABLE products RENAME COLUMN product_no TO product_number;
```

##### 5.5.8 Renaming a Table

给表重命名命令如下:

```sql
ALTER TABLE products RENAME TO items;
```

##### 5.5.9 Adding a Node to Distribution Target

要将新节点添加到已有分布式中：

```sql
ALTER TABLE products ADD NODE (datanode_3);
```

数据节点必须是有效的数据节点名称。如果它是一个新的数据节点，则必须通过适当的机制将其添加到群集中。

此命令将重新分发现有的表数据，以包括新的节点。例如，在使用DISTRIBUTE BY HASH策略的情况下，必须重新计算每行的哈希值，并且必须基于分发列表中新的数据节点数将行移动到所选节点。此操作需要对表进行排他锁定，因此对表的所有读取和写入访问都将被暂时阻止。这在表很大且需要大量时间进行重新分配的情况下尤其重要。

##### 5.5.10 Removing a Node from Distribution Target

要从现有分布式中删除节点：

```sql
ALTER TABLE products DELETE NODE (datanode_3);
```

必须注意，如果要从群集中删除节点，重要的是首先使用该节点从所有现有表中删除该节点，然后再从群集中删除该节点。

像前面的命令一样，这也需要对表进行排他锁，从而阻止对表的所有读取和写入访问。

##### 5.5.11 Changing Distribution Strategy

要更改表的分配策略：

```sql
ALTER TABLE products DISSTRIBUTE BY REPLICATION;
```

您可以使用此命令更改表的分配策略。例如，以前由HASH分发的表现在可以由REPLIC