3
函数索引
生成的列是一个特殊的列，它总是从其他列计算而来。因此，它对于列就像视图对于表一样。生成的列有两种:
存储列和虚拟列。存储生成的列在写入
(
插入或更新
)
时计算，并像普通列一样占用存储空间。虚拟生成的列不占用任何存储空间，并且在读取时进行计算。因此，虚拟生成的列类似于视图，存储生成的列类似于物化视图 (
除了它总是自动更新
)
。
KingbaseES
目前只实现存储生成的列。
要创建一个函数索引，使用
GENERATED ALWAYS AS
作为
CREATE TABLE
的子句，例如
:
CREATE TABLE people (
...
,
height_cm numeric,
height_in numeric GENERATED ALWAYS AS (height_cm
/
2.54
) STORED
);
必须指定关键字
STORED
来选择所生成列的存储类型。详见
CREATE TABLE
。
生成的列不能直接写入。在
INSERT
或
UPDATE
命令中，不能为生成的列指定值，但是可以指定关键字缺省值。不能为生成的列指定值，但是可以指定关键字缺省值。
考虑具有默认值的列与生成的列之间的差异。如果没有提供其他值，则在首次插入行时计算一次列默认值
;
生成的列在行发生更改时更新，并且不能被覆盖。列的默认值不能引用表中的其他列
;
一个代表达式通常会这样做。列默认可以使用 volatile
函数，例如
random()
或当前时间的函数，这对于生成的列是不允许的。
一些限制适用于定义生成的列和涉及生成列的表
:
•
生成表达式只能使用不可变函数，不能使用子查询或以任何方式引用当前行以外的任何内容。
•
生成表达式不能引用另一个生成的列。
•
生成表达式不能引用系统列，除了
tableoid
.
•
生成的列不能具有列默认值或标识定义。
•
生成的列不能是分区键的一部分。
•
外部表可以生成列。详见
CREATE FOREIGNTABLE
。
在使用生成的列时需要考虑其他问题。
•
生成的列独立于其底层基本列维护访问特权。因此，可以对其进行安排，使特定的角色可以从生成的列读取数据，但不能从底层的基本列读取数据。
•
在触发器运行之前，在之后更新生成的列。因此，在触发器之前对中的基本列所做的更改将反映在生成的列中。但相反，在触发器之前，不允许访问中生成的列。
4
约束
数据类型是一种限制能够存储在表中数据类别的方法。但是对于很多应用来说，它们提供的约束太粗糙。例如，一个包含产品价格的列应该只接受正值。但是没有任何一种标准数据类型只接受正值。另一个问题是我们可能需要根据其他列或行来约束一个列中的数据。例如，在一个包含产品信息的表中，对于每个产品编号
应该只有一行。到目前为止，SQL
允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。即便是这个值来自于默认值定义，这个规则也同样适用。
4.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)
);
前两个约束看起来很相似。第三个则使用了一种新语法。它并没有依附在一个特定的列，而是作为一个独立
的项出现在逗号分隔的列列表中。列定义和这种约束定义可以以混合的顺序出现在列表中。
我们将前两个约束称为列约束，而第三个约束为表约束，因为它独立于任何一个列定义。列约束也可以写成
表约束，但反过来不行，因为一个列约束只能引用它所依附的那一个列（
KingbaseES
并不强制要求这个规则，
但是如果我们希望表定义能够在其他数据库系统中工作，那就应该遵循它）。上述例子也可以写成：
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)
);
需要注意的是，一个检查约束在其检查表达式值为真或空值时被满足。因为当任何操作数为空时大部分表达
式将计算为空值，所以它们不会阻止被约束列中的控制。为了保证一个列不包含控制，可以使用下一节中的
非空约束。
Note:
KingbaseES
不支持检查引用表数据的约束，而不是检查新的或更新的行。虽然检查违反此规则的约束在简单测试中可能有效，但它不能保证数据库不会达到约束条件为 false
的状态
(
由于涉及的其他行随后发生
了更改
)
。这将导致数据库转储和重新加载失败。即使整个数据库状态与约束一致，重新加载也可能失败，因为没有按满足约束的顺序加载行。如果可能的话,
使用
UNIQUE
、
EXCLUDE
或
FOREIGN KEY
表示跨行和
跨表限制的约束。如果您希望在插入行时对其他行进行一次性检查，而不是持续维护一致性保证，一个
触发器
惯例可以用来实
现它。
(
此方法避免了转储
/
重新加载问题，因为
sys_dump
在重新加载数据之前不会重新安装触发器，因此在转储/
重新加载期间不会强制执行检查。
)
Note:
KingbaseES
假设检查约束条件是不可变的，也就是说，对于相同的输入行，它们总是会给出相同的结果。这种假设是检查检查约束的理由，只在插入或更新行时，而不是在其他时候。(
上面关于不引用其他表数
据的警告实际上是这种限制的一个特例。
)
打破这种假设的一个常见方法是在
CHECK
表达式中引用一个用户定义的函数，然后改变函数的行为。
KingbaseES
不允许，但它不会注意到，如果有行在表中，现在违反了
CHECK
约束。这将导致随后的数据库
转储和重新加载失败。处理这种更改的推荐方法是删除约束
(
使用
ALTER TABLE
)
，调整函数定义，并重新
添加约束，从而对所有表行重新检查约束。
4.2
非空约束
一个非空约束仅仅指定一个列中不会有空值。语法例子：
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
一个非空约束总是被写成一个列约束。一个非空约束等价于创建一个检查约束
CHECK (column_name
IS
NOT NULL)
，但在
KingbaseES
中创建一个显式的非空约束更高效。这种方式创建的非空约束的缺点是我们
无法为它给予一个显式的名称。
当然，一个列可以有多于一个的约束，只需要将这些约束一个接一个写出：
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price
>
0
)
);
约束的顺序没有关系，因为并不需要决定约束被检查的顺序。
NOT NULL
约束有一个相反的情况：
NULL
约束。这并不意味着该列必须为空，进而肯定是无用的。相反，它
仅仅选择了列可能为空的默认行为。
SQL
标准中并不存在
NULL
约束，因此它不能被用于可移植的应用中
（
KingbaseES
中加入它是为了和某些其他数据库系统兼容）。但是某些用户喜欢它，因为它使得在一个脚本文
件中可以很容易的进行约束切换。例如，初始时我们可以：
CREATE TABLE products (
product_no integer NULL,
name text NULL,
price numeric NULL
);
然后可以在需要的地方插入
NOT
关键词。
Tip:
在大部分数据库中多数列应该被标记为非空。
4.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
);
增加一个唯一约束会在约束中列出的列或列组上自动创建一个唯一
B-tree
索引。只覆盖某些行的唯一性限制
不能被写为一个唯一约束，但可以通过创建一个唯一的
部分索引
来强制这种限制。
通常，如果表中有超过一行在约束所包括列上的值相同，将会违反唯一约束。但是在这种比较中，两个空值
被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。
这种行为符合
SQL
标准，但我们听说一些其他
SQL
数据库可能不遵循这个规则。所以在开发需要可移植的
应用时应注意这一点。
4.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,
(continues on next page)
(continued from previous page)
b integer,
c integer,
PRIMARY KEY (a, c)
);
增加一个主键将自动在主键中列出的列或列组上创建一个唯一
B-tree
索引。并且会强制这些列被标记为
NOT NULL
。
一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但
只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主键。但
KingbaseES
中并未强制要求
这一点，但是最好能够遵循它。
主键对于文档和客户端应用都是有用的。例如，一个允许修改行值的
GUI
应用可能需要知道一个表的主键，
以便能唯一地标识行。如果定义了主键，数据库系统也有多种方法来利用主键。例如，主键定义了外键要引
用的默认目标列。
4.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,
(continues on next page)
(continued from previous page)
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,
(continues on next page)
(continued from previous page)
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
一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立
合适的索引也会大有益处。由于这种做法并不是必须的，而且创建索引也有很多种选择，所以外键约束的定
义并不会自动在引用列上创建索引。
4.6
排它约束
排它约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返
回否或空值。语法是：
CREATE TABLE circles (
c circle,
EXCLUDE USING gist (c WITH
&&
)
);
详见
CREATE TABLE
中
CREATE TABLE ... CONSTRAINT ... EXCLUDE
。
增加一个排它约束将在约束声明所指定的类型上自动创建索引。
4.7
启用
/
禁用约束
约束禁用是为了提高性能，特别是在数据仓库中，这种操作的作用尤其明显。在约束启用的情况下，如果对
表进行大规模的写操作（
copy/insert/update
等），约束的检查会占用一定比例的时间，这在数据仓库中尤为明
显。反之，在大规模的写操作前将约束禁用，在写操作结束后再启用约束，则可以获得性能收益。
在停用约束时，违反约束的行可以插入到表中，这种行为被认为是约束异常。而且如果约束处于启用非校验
（
NOVALIDATE
）状态，则在停用时插入的违反约束的记录仍然保留，需要更改后，约束方可处在已校验状
态。
当表的约束处于启用非校验状态时，表中已有的数据可以不符合约束，但后面新增的数据必须进行校验，这
对于数据仓库来说特别有用。因为基本上数据仓库都是使用增量更新，在停用约束后如果采用启用校验约束
状态，则需要对大量数据进行校验，影响性能。
启用禁用约束可以在创建表的时候指定初始状态。语法是：
CREATE TABLE t1 (
i
int
primary key disable novalidate,
j
int
constraint t1_j_check check (j
>
1
)
) ENABLE NOVALIDATE constraint t1_j_check;
也可以在表建好以后使用
ALTER TABLE
语法修改约束状态。语法是：
ALTER TABLE t1 ENABLE VALIDATE primary key;
ALTER TABLE t1 MODIFY CONSTRAINT t1_j_check DISABLE;
ALTER TABLE t1 ADD CONSTRIANT t1_j_key UNIQUE (j);
Note:
KingbaseES
只支持检查约束、唯一约束、主键和外键约束的启用
/
禁用和有效性状态设置或修改操作。
扫一扫
1318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
