Postgre约束详解

数据类型是限制我们可以在表里存储什么数据的一种方法。不过，对于许多应用来说，这种限制实在是太粗糙了。比如，一个包含产品价格的字段应该只接受正数。但是没有哪种标准数据类型只接受正数。另外一个问题是你可能需要根据其它字段或者其它行的数据来约束字段数据。比如，在一个包含产品信息的表中，每个产品编号都应该只有一行。

对于这些问题，SQL 允许你在字段和表上定义约束。约束允许你对数据施加任意控制。如果用户企图在字段里存储违反约束的数据，那么就会抛出一个错误。这种情况同时也适用于数值来自缺省值的情况。

1. 检查约束

检查约束是最常见的约束类型。它允许你声明在某个字段里的数值必须使一个布尔表达式为真。比如，要强制一个正数的产品价格，你可以用：

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

如你所见，约束定义在数据类型之后，就好像缺省值定义一样。缺省值和约束可以按任意顺序排列。一个检查约束由一个关键字CHECK后面跟一个放在圆括弧里的表达式组成。检查约束表达式应该包含受约束的字段，否则这个约束就没什么意义了。

你还可以给这个约束取一个独立的名字。这样就可以令错误消息更清晰，并且在你需要修改它的时候引用这个名字。语法是：

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

因此，要声明一个命名约束，使用关键字CONSTRAINT后面跟一个标识符(作为名字)，然后再跟约束定义。如果你不用这个方法声明约束，那么系统会自动为你选择一个名字。

一个检查约束也可以引用多个字段。假设你存储一个正常价格和一个折扣价，并且你想保证折扣价比正常价低：

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并不强制这条规则，但是如果你希望自己的表定义可以和其它数据库系统兼容，那么你最好还是遵循这条规则。上面的例子也可以这么写：

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

或者是：

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

这只是风格的不同。

和字段约束一样，我们也可以给表约束赋予名称，方法也相同：

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 ，所以这些约束不能阻止字段值为 NULL 。要确保一个字段值不为 NULL ，可以使用下一节介绍的非空约束。

2. 非空约束

非空约束只是简单地声明一个字段必须不能是 NULL。下面是一个例子：

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

一个非空约束总是写成一个字段约束。非空约束在功能上等效于创建一个检查约束 CHECK (column_name IS NOT NULL)，但在PostgreSQL里，创建一个明确的非空约束效率更高。缺点是你不能给它一个明确的名字。

当然，一个字段可以有多个约束。只要一个接着一个写就可以了：

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

它们的顺序无所谓。顺序并不影响约束检查的顺序。

NOT NULL约束有个相反的约束：NULL约束。它并不意味着该字段必须是空，因为这样的字段也没用。它只是定义了该字段可以为空的这个缺省行为。在 SQL 标准里没有定义NULL约束，因此不应该在可移植的应用中使用它。在PostgreSQL里面增加这个约束只是为了和其它数据库系统兼容。不过，有些用户喜欢它，因为这个约束可以让他们很容易在脚本文件里切换约束。比如，你可以从下面这样开始：

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

然后在需要的时候插入NOT关键字。

提示: 在大多数数据库设计里，主要的字段都应该标记为非空。

3. 唯一约束

唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。它的语法是：

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

上面是写成字段约束，下面这个则写成表约束：

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

如果一个唯一约束引用一组字段，那么这些字段用逗号分隔列出：

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

这样就声明了特定字段值的组合在整个表范围内是唯一的。但是这些字段中的某个单独值可以不必是(并且通常也确实不是)唯一的。

你也可以给唯一约束赋予一个自己定义的名字，方法与前面相同：

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

添加一个唯一约束通常会自动在约束中使用的列或一组列上创建一个唯一btree索引。可以通过创建一个部分索引强制仅在某些行上创建唯一约束。

通常，如果包含在唯一约束中的那几个字段在表中有多个相同的行，就违反了唯一约束。但是在这种比较中，NULL 被认为是不相等的。这就意味着，在多字段唯一约束的情况下，如果在至少一个字段上出现 NULL ，那么我们还是可以存储同样的这种数据行。这种行为遵循 SQL 标准，但是我们听说其它 SQL 数据库可能不遵循这个标准。因此如果你要开发可移植的程序，那么最好仔细些。

4. 主键

从技术上讲，主键约束只是唯一约束和非空约束的组合。所以，下面两个表定义是等价的：

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
);

主键也可以约束多于一个字段；其语法类似于唯一约束：

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

主键表示一个或多个字段的组合可以用于唯一标识表中的数据行。这是定义一个主键的直接结果。请注意：一个唯一约束实际上并不能提供一个唯一标识，因为它不排除 NULL 。这个功能对文档目的和客户应用都很有用。比如，一个可以修改行数值的 GUI 应用可能需要知道一个表的主键才能唯一地标识每一行。

添加一个主键将会在主键使用的列或一组列中自动创建一个唯一btree索引。

一个表最多可以有一个主键(但是它可以有多个唯一和非空约束)。关系型数据库理论告诉我们，每个表都必须有一个主键。PostgreSQL并不强制这个规则，但我们最好还是遵循它。

5. 外键

外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中出现的数值。我们把这个行为称为两个相关表之间的参照完整性。

假设你有个产品表，我们可能使用了好几次：

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

假设你有一个存储这些产品的订单的表。我们想保证订单表只包含实际存在的产品。因此我们在订单表中定义一个外键约束引用产品表：

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

现在，我们不能创建任何其非空product_no记录没有在产品表中出现的订单。

在这种情况下我们把订单表叫做引用表，而产品表叫做被引用表。同样，也有引用字段和被引用字段。

你也可以把上面的命令简写成：

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

因为如果缺少字段列表的话，就会引用被引用表的主键。

一个外键也可以约束和引用一组字段。同样，也需要写成表约束的形式。下面是一个捏造出来的语法例子：

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

当然，被约束的字段数目和类型需要和被引用字段数目和类型一致。

和平常一样，你也可以给外键约束赋予自定义的名字。

一个表可以包含多于一个外键约束。这个特性用于实现表之间的多对多关系。比如你有关于产品和订单的表，但现在你想允许一个订单可以包含多种产品 (上面那个结构是不允许这么做的)，你可以使用这样的结构：

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)
);

注意最后的表的主键和外键是重叠的。

我们知道外键不允许创建和任何产品都无关的订单。但是如果一个订单创建之后其引用的产品被删除了怎么办？SQL 也允许你处理这个问题。简单说，我们有几种选择：

不允许删除一个被引用的产品
同时也删除订单
其它的?

为了说明这个问题，我们对上面的多对多关系制定下面的策略：如果有人想删除一种仍然被某个订单引用的产品(通过order_items)，那么就不允许这么做。如果有人删除了一个订单，那么订单项也被删除。

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和non-null值的混合肯定不能满足MATCH FULL约束）。如果你不想引用行能够避免满足外键约束，那么声明引用行为NOT NULL。

一个外键必须要么引用一个主键，要么引用一个唯一约束。这意味着被引用行总是有一个索引（一个基本的主键或唯一约束）；所以检查一个引用行是否有一个匹配是高效的。因此从被引用表中DELETE一个行或者一个被引用字段UPDATE一列，都需要扫描一次引用表以便从行中匹配老的数值，给引用字段创建索引也是一个好主意。因为这个不是总是被需要，而且怎么去建立索引还有许多其他的选择，外键约束的声明不能在引用字段上自动生成一个索引。

6. 排除约束

排他约束保证如果任何两行被在声明的字段里比较或者用声明的操作表达，至少有一个操作比较会返回错误或空值。句法是：

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

参考资料http://www.infocool.net/PostgreSQL/index.htm

posted @ 2016-06-12 12:02 韦宇阅读(1088) 评论(0) 编辑收藏举报

刷新页面返回顶部

韦宇