postgresql笔记014—约束

约束

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

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

检查约束

检查约束是最常见的约束类型。检查约束允许我们在声明某个字段里的数值必须使用一个布尔表达式为真。
比如:强制要求一个正整数的的商品价格。我们可以对价格做如下的定义：
案例：
创建有约束的表：

create table products(
	product_no integer,
	name text,
	price numeric check(price >0)
);

向表中插入不符合约束条件的数据：

insert into test.products values(30,'小明',-100);

运行结果：

如上所示，约束定义在数据类型之后，就好像省缺值定义一样。省缺值和约束可以按任意顺序排序。
一个检查约束由一个关键字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 (discount_price >0),
	check (price >discount_price)
);

等价于下面方式

create table products(
	product_no integer,
	name text,
	price numeric check (price >0),
	discount_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，所以这些约束条件不能阻止字段值为NULL。要确保一个字段值不为NULL，可以使用非空约束。

非空约束

非空约束只是简单地声明一个字段必须不能是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关键字。

唯一约束

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

使用案例：

create table products(
	product_no integer unique,
	name text,
	price numeric
);

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

create table products (
	product_no integer,
	name text,
	price numeric,
	nuique (product_no)
);

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

create table example (
	a integer,
	b integer,
	c integer,
	unique(a, c)
);

这样就声明了特定字段值得组合在整张表范围内是唯一的。
但是这些字段中的某个单独值可以不必是唯一的。
向example表中插入数据：

insert into test.example values(1,1,1);
insert into test.example values (1,2,2);

执行结果(表中的值正确)：

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

create table products(
	product_no integer constraint must_be_different unique,
	name text,
	price numeric
);

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

通常，如果包含在唯一约束中的那几个字段在表中有多个相同的行，就违反了唯一约束。
但是在这种比较中，NULL别认为是不相等的。这就意味着，在多字段唯一约束的情况下，如果在至少一个字段上出现NULL，那么我们还是可以存储同样的这种数据行。这种行为遵循sql标准。
但是我们听说其它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
);

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

create table example (
	a integer,
	b integer,
	c integer,
	primary key (a , c)
);

主键表示一个或者多个字段的组合可以用于唯一标识表中的数据行。 这是定义一个主键的直接结果。

注意：一个唯一约束实际上并不能提供一个唯一标识，因为他不排除NULL 。这个功能对文档的目的和客户应用都很有用。
比如：一个可以修改行数值的GUI应用可能需要知道一个表的主键才能唯一地标识每一行。

添加一个主键将会在主键使用的列或一组列中自动创建一个唯一btree索引。
（当主键约束多个列时，只有所有的列值都相同，才认为索引相同，不能再进行插入。如果一个主键约束多个列，一个列相同的情况下，依然可以继续插入数据。）

一个表最多有一个主键（但是它可以有多个唯一和非空约束）。

关系型数据库理论告诉我们，每个表都必须有一个主键。postgresql并不强制这个规则，但是我们最好遵守这个规则。

外键

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

假设我们有一个产品表，我们要使用好多次：

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

在上表中通过references products (product_no) 将orders表中的product_no列声明为外键。这样我们就不能创建(或添加)任何其非空product_no记录没有在产品表products中出现的订单了。

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

同时我们也可以将上面的创建orders表简写为：

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允许我们处理这样的问题。我们有如下选择：
1.不允许删除一个被引用的产品
2.同时也删除订单
3.其它？

为了说明这给我问题，我们对上面的多对多关系制定下面的策略：如果有人想删除一种仍然被某个订单引用的产品（通过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意味着被引用字段的更新后的值，应该被copy到引用行中。

通常，如果一个引用行的任意引用字段为null,那么这个引用行不必满足外键约束。如果外键声明中添加了 match full,引用行只有在所有的引用字段都是null时，才能满足条比满足约束。
如果你不想引用行能够避免满足外键约束，那么声明引用行为not null。

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

排除约束

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

create table circles(
	c circle,
	exclude using gist (c with &&)
);

添加一个排除约束会在约束声明里自动创建一个声明类型的索引。