postgresql 约束、主键、外键

由于工作需要，近期在学习postgresql数据库方面的东西，一下为学习过程中摘录的一些东西，希望记录自己学习的过程，同时可以帮助有需要这方便帮助的小伙伴。

以下文章为摘录，并非原创。

出处http://www.postgres.cn/docs/10/ddl-constraints.html

表基础

要创建一个表，我们要用到CREATE TABLE命令。在这个命令中 我们需要为新表至少指定一个名字、列的名字及数据类型。例如：

CREATE TABLE products (

    product_no integer,

    name text,

    price numeric

);

（numeric类型能够存储小数部分，典型的例子是金额。）

如果我们不再需要一个表，我们可以通过使用DROP TABLE命令来移除它。例如： DROP TABLE products;

尝试移除一个不存在的表会引起错误。然而，在SQL脚本中在创建每个表之前无条件地尝试移除它的做法是很常见的，即使发生错误也会忽略之，因此这样的脚本可以在表存在和不存在时都工作得很好（如果你喜欢，可以使用DROP TABLE IF EXISTS变体来防止出现错误消息，但这并非标准SQL）。

约束

到目前为止，SQL允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。即便是这个值来自于默认值定义，这个规则也同样适用。

5.3.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)

);

需要注意的是，一个检查约束在其检查表达式值为真或空值时被满足。因为当任何操作数为空时大部分表达式将计算为空值，所以它们不会阻止被约束列中的控制。为了保证一个列不包含控制，可以使用下一节中的非空约束。

5.3.2. 非空约束

一个非空约束仅仅指定一个列中不会有空值。语法例子：

 

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关键词。

提示

在大部分数据库中多数列应该被标记为非空。

 唯一约束

 

唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。写成一个列约束的语法是：

 

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

);

 

增加一个唯一约束会在约束中列出的列或列组上自动创建一个唯一B-tree索引。只覆盖某些行的唯一性限制不能被写为一个唯一约束，但可以通过创建一个唯一的部分索引来强制这种限制。

 

通常，如果表中有超过一行在约束所包括列上的值相同，将会违反唯一约束。但是在这种比较中，两个空值被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。这种行为符合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)

);

 

增加一个主键将自动在主键中列出的列或列组上创建一个唯一B-tree索引。并且会强制这些列被标记为NOT NULL。

 

一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主键。但PostgreSQL中并未强制要求这一点，但是最好能够遵循它。

 

主键对于文档和客户端应用都是有用的。例如，一个允许修改行值的 GUI 应用可能需要知道一个表的主键，以便能唯一地标识行。如果定义了主键，数据库系统也有多种方法来利用主键。例如，主键定义了外键要引用的默认目标列。

外键

 

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。

 

例如我们有一个使用过多次的产品表：

 

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。这些将导致在被引用行被删除后，引用行中的引用列被置为空值或它们的默认值。注意这些并不会是我们免于遵守任何约束。例如，如果一个动作指定了SET DEFAULT，但是默认值不满足外键约束，操作将会失败。

与ON DELETE相似，同样有ON UPDATE可以用在一个被引用列被修改（更新）的情况，可选的动作相同。在这种情况下，CASCADE意味着被引用列的更新值应该被复制到引用行中。

正常情况下，如果一个引用行的任意一个引用列都为空，则它不需要满足外键约束。如果在外键定义中加入了MATCH FULL，一个引用行只有在它的所有引用列为空时才不需要满足外键约束（因此空和非空值的混合肯定会导致MATCH FULL约束失败）。如果不希望引用行能够避开外键约束，将引用行声明为NOT NULL。

一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一个索引（位于主键或唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查将会很高效。由于从被引用表中DELETE一行或者UPDATE一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立合适的索引也会大有益处。由于这种做法并不是必须的，而且创建索引也有很多种选择，所以外键约束的定义并不会自动在引用列上创建索引。

 

更多关于更新和删除数据的信息请见第 6 章。外键约束的语法描述请参考CREATE TABLE。

排他约束

 

排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。语法是：

 

CREATE TABLE circles (

    c circle,

    EXCLUDE USING gist (c WITH &&)

);

 

详见CREATE TABLE ... CONSTRAINT ... EXCLUDE。

 

增加一个排他约束将在约束声明所指定的类型上自动创建索引。