SQL中的问题

发布网友发布时间：2022-04-08 09:04

共1个回答

热心网友时间：2022-04-08 10:34

1. 检查约束
检查约束是最常见的约束类型。它允许你声明在某个字段里的数值必须使一个布尔表达式为真。比如，要强制一个正数的产品价格，你可以用：
CREATE TABLE procts ( proct_no integer, name text, price numeric CHECK (price > 0) );
如你所见，约束定义在数据类型之后，就好像缺省值定义一样。缺省值和约束可以按任意顺序排列。一个检查约束由一个关键字 CHECK 后面跟一个放在圆括弧里的表达式组成。检查约束表达式应该包含受约束的字段，否则这个约束就没什么意义了。
你还可以给这个约束取一个独立的名字。这样就可以令错误信息更清晰，并且在你需要修改它的时候引用这个名字。语法是：
CREATE TABLE procts ( proct_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) );
因此，要声明一个命名约束，使用关键字 CONSTRAINT 后面跟一个标识符(作为名字)，然后再跟约束定义。如果你不用这个方法声明约束，那么系统会自动为你选择一个名字。
一个检查约束也可以引用多个字段。假设你存储一个正常价格和一个折扣价，并且你想保证折扣价比正常价低。
CREATE TABLE procts ( proct_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );
头两个约束看上去很面熟。第三个使用了一个新的语法。它没有附着在某个字段上，而是在逗号分隔的字段列表中以一个独立行的形式出现。字段定义和约束定义可以按照任意顺序列出。
我们称头两个约束是"字段约束"，而第三个约束是"表约束"(和字段定义分开写)。字段约束也可以写成表约束，而反过来很可能不行，因为系统假设字段约束只引用它所从属的字段。PostgreSQL 并不强制这条规则，但是如果你希望自己的表定义可以和其它数据库系统兼容，那么你最好还是遵循这条规则。上面的例子也可以这么写：
CREATE TABLE procts ( proct_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price) ); 或者是
CREATE TABLE procts ( proct_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) );
这只是风格的不同。
和字段约束一样，我们也可以给表约束赋予名称，方法也相同：
CREATE TABLE procts ( proct_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 procts ( proct_no integer NOT NULL, name text NOT NULL, price numeric );
一个非空约束总是写成一个字段约束。非空约束在功能上等效于创建一个检查约束 CHECK (column_name IS NOT NULL) ，但在 PostgreSQL 里，创建一个明确的非空约束效率更高。缺点是你不能给它一个明确的名字。
当然，一个字段可以有多个约束。只要一个接着一个写就可以了：
CREATE TABLE procts ( proct_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) );
它们的顺序无所谓。顺序并不影响约束检查的顺序。
NOT NULL 约束有个相反的约束：NULL 约束。它并不意味着该字段必须是空，因为这样的字段也没用。它只是定义了该字段可以为空的这个缺省行为。在 SQL 标准里没有定义 NULL 约束，因此不应该在可移植的应用中使用它。在 PostgreSQL 里面增加这个约束只是为了和其它数据库系统兼容。不过，有些用户喜欢它，因为这个约束可以让他们很容易在脚本文件里切换约束。比如，你可以从下面这样开始
CREATE TABLE procts ( proct_no integer NULL, name text NULL, price numeric NULL );
然后在需要的时候插入 NOT 关键字。

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

3. 唯一约束
唯一约束保证在一个字段或者一组字段里的数据与表中其它行的数据相比是唯一的。它的语法是：
CREATE TABLE procts ( proct_no integer UNIQUE, name text, price numeric );
上面是写成字段约束，下面这个则写成表约束：
CREATE TABLE procts ( proct_no integer, name text, price numeric, UNIQUE (proct_no) );
如果一个唯一约束引用一组字段，那么这些字段用逗号分隔列出：
CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );
这样就声明了特定字段值的组合在整个表范围内是唯一的。但是这些字段中的某个单独值可以不必是(并且通常也确实不是)唯一的。
你也可以给唯一约束赋予一个自己定义的名字，方法与前面相同：
CREATE TABLE procts ( proct_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric );
通常，如果包含在唯一约束中的那几个字段在表中有多个相同的行，就违反了唯一约束。但是在这种比较中，NULL 被认为是不相等的。这就意味着，在多字段唯一约束的情况下，如果在至少一个字段上出现 NULL ，那么我们还是可以存储同样的这种数据行。这种行为遵循 SQL 标准，但是我们听说其它 SQL 数据库可能不遵循这个标准。因此如果你要开发可移植的程序，那么最好仔细些。

4. 主键
从技术上讲，主键约束只是唯一约束和非空约束的组合。所以，下面两个表定义是等价的：
CREATE TABLE procts ( proct_no integer UNIQUE NOT NULL, name text, price numeric ); CREATE TABLE procts ( proct_no integer PRIMARY KEY, name text, price numeric );
主键也可以约束多于一个字段；其语法类似于唯一约束：
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );
主键表示一个或多个字段的组合可以用于唯一标识表中的数据行。这是定义一个主键的直接结果。请注意：一个唯一约束实际上并不能提供一个唯一标识，因为它不排除 NULL 。这个功能对文档目的和客户应用都很有用。比如，一个可以修改行数值的 GUI 应用可能需要知道一个表的主键才能唯一地标识每一行。
一个表最多可以有一个主键(但是它可以有多个唯一和非空约束)。关系型数据库理论告诉我们，每个表都必须有一个主键。PostgreSQL 并不强制这个规则，但我们最好还是遵循它。

5. 外键
外键约束声明一个字段(或者一组字段)的数值必须匹配另外一个表中出现的数值。我们把这个行为称为两个相关表之间的参照完整性。
假设你有个产品表，我们可能使用了好几次：
CREATE TABLE procts ( proct_no integer PRIMARY KEY, name text, price numeric );
假设你有一个存储这些产品的订单的表。我们想保证订单表只包含实际存在的产品。因此我们在订单表中定义一个外键约束引用产品表：
CREATE TABLE orders ( order_id integer PRIMARY KEY, proct_no integer REFERENCES procts (proct_no), quantity integer );
现在，我们不能创建任何其 proct_no 没有在产品表中出现的订单。
在这种情况下我们把订单表叫做引用表，而产品表叫做被引用表。同样，也有引用字段和被引用字段。
你也可以把上面的命令简写成
CREATE TABLE orders ( order_id integer PRIMARY KEY, proct_no integer REFERENCES procts, quantity integer );
因为如果缺少字段列表的话，就会引用被引用表的主键。
一个外键也可以约束和引用一组字段。同样，也需要写成表约束的形式。下面是一个捏造出来的语法例子：
CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );
当然，被约束的字段数目和类型需要和被引用字段数目和类型一致。
和平常一样，你也可以给外键约束赋予自定义的名字。
一个表可以包含多于一个外键约束。这个特性用于实现表之间的多对多关系。比如你有关于产品和订单的表，但现在你想允许一个订单可以包含多种产品(上面那个结构是不允许这么做的)，你可以使用这样的结构：
CREATE TABLE procts ( proct_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( proct_no integer REFERENCES procts, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (proct_no, order_id) );
注意最后的表的主键和外键是重叠的。
我们知道外键不允许创建和任何产品都无关的订单。但是如果一个订单创建之后其引用的产品被删除了怎么办？SQL 也允许你处理这个问题。简单说，我们有几种选择：

不允许删除一个被引用的产品
同时也删除订单
其它的?
为了说明这个问题，我们对上面的多对多关系制定下面的策略：如果有人想删除一种仍然被某个订单引用的产品(通过 order_items)，那么就不允许这么做。如果有人删除了一个订单，那么订单项也被删除。
CREATE TABLE procts ( proct_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( proct_no integer REFERENCES procts ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (proct_no, order_id) );
*和级联删除是两种最常见的选项。RESTRICT 禁止删除被引用的行。NO ACTION 的意思是如果在检查约束的时候还存在任何引用行，则抛出错误；如果你不声明任何东西，那么它就是缺省的行为。这两个选择的实际区别是：NO ACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。CASCADE 声明在删除一个被引用的行的时候，所有引用它的行也会被自动删除掉。在外键字段上的动作还有两个选项：SET NULL 和 SET DEFAULT ，它们导致在被引用行删除的时候，将引用它们的字段分别设置为 NULL 和缺省值。请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SET DEFAULT ，但是缺省值并不能满足外键，那么该动作就会失败。
与 ON DELETE 类似的还有 ON UPDATE 选项，它是在被引用字段修改(更新)的时候调用的，可用的动作是一样的。
有关更新和删除数据的更多信息可以在章6里找到。
最后，我们应该说明的是，一个外键必须要么引用一个主键，要么引用一个唯一约束。如果外键引用了一个唯一约束，那么在如何匹配 NULL 这个问题上还有一些其它的可能性。这些东西都在 CREATE TABLE 中解释。

参考资料：网络