缺省值

一个列可以赋予缺省值。如果新创建了一个数据行,而有些列的数值没有声明,那么这些列将被填充为它们各自的缺省值。一条数据修改命令也可以明确地为一个列设置为它的缺省值,而不用事先知道这个缺省值是什么。

如果没有明确声明缺省值,那么缺省值是 NULL 。这么做通常是合理的,因为 NULL 表示”未知”。

在一个表定义里,缺省值是在列数据类型后面列出的。比如:

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

缺省值可以是一个表达式,它会在插入缺省值的时候计算(不是在创建表的时候)。一个常见的例子是一个timestamp列可能有缺省值now(),它表示插入行的时刻。另外一个常见的例子是为每一行生成一个”序列号”。在OushuDB里,通常是用类似下面这样的方法生成的:

CREATE TABLE products (
    product_no integer DEFAULT nextval('products_product_no_seq'),
    ...
);

这里的nextval()从一个 序列对象 提供后继的数值。这种做法非常普遍,以至于我们有一个专门的缩写用于此目的:

CREATE TABLE products (
    product_no SERIAL,
    ...
);

Note

MAGMA格式的表不支持为列设置缺省值。

约束

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

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

Note

MAGMA格式的表不支持列约束,但允许主键约束。

检查约束

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

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

头两个约束看上去很面熟。第三个使用了一个新的语法。它没有附着在某个列上,而是在逗号分隔的列定义列表中以一个独立行的形式出现。列定义和约束定义可以按照任意顺序列出。

我们称头两个约束是”列约束”,而第三个约束是”表约束”,因为它和任何一个列定义都是分离的。列约束也可以写成表约束,而反过来很可能不行,因为系统假设列约束只引用它所从属的列。 OushuDB并不强制这条规则,但是如果你希望自己的表定义可以和其它数据库系统兼容,那么你最好还是遵循这条规则。上面的例子也可以这么写:

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 ,可以使用下面介绍的非空约束。

Note

MAGMA格式的表不支持列约束。

非空约束

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

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

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

当然,一个列可以有多个约束。只要一个接着一个写就可以了:

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

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

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

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

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

Tip

在大多数数据库设计里,主要的列都应标记为非空。

Note

MAGMA格式的表不支持非空约束。

主键

理论上讲,主键约束等价于唯一约束和非空约束的结合。在OushuDB支持的四种格式(AO,ORC,MAGMA,PARQUET)中,只有MAGMA表支持主键约束,而且在创建MAGMA表时,可以声明主键列,且主键约束的列里数据类型为非定长时需要将该列放置最后一列。比如:

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

主键约束可以同时约束多个列:

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

主键意味着表中的一列或一组列可以用作表中每行的唯一标识。(这是主键定义的直接结果)。主键约束与唯一约束不同的是,唯一约束不排除null值。这对于记录目的和用户程序都可用。比如,一个GUI应用程序可能需要知道表中的主键来唯一确定需要修改的行。