PGSQL5.1学习笔记:数据定义

1、表基础

关系型数据库中的一个表非常像纸上的一张表：它由行和列组成。列的数量和顺序是固定的，并且每一列拥有一个名字。行的数目是变化的，它反映了在一个给定时刻表中存储的数据量。
每一列都有一个数据类型。数据类型约束着一组可以分配给列的可能值，并且它为列中存储的数据赋予了语义，这样它可以用于计算。

一些常用的数据类型是：
用于整数的integer；
可以用于分数的numeric；
用于字符串的text，
用于日期的date，
用于一天内时间的time
可以同时包含日期和时间的timestamp。

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

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

使用DROP TABLE命令来删除表。

DROP TABLE products;

2、默认值

一个列可以被分配一个默认值。当一个新行被创建且没有为某些列指定值时，这些列将会被它们相应的默认值填充。
如果没有显式指定默认值，则默认值是空值。

在一个表定义中，默认值被列在列的数据类型之后。

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

默认值可以是一个表达式，它将在任何需要插入默认值的时候被实时计算。
一个常见的例子是为一个timestamp列指定默认值为CURRENT_TIMESTAMP，这样它将得到行被插入时的时间。
另一个常见的例子是为每一行生成一个“序列号” :
DEFAULT nextval(‘products_product_no_seq’) 。

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

还有一种特别的速写：

CREATE TABLE products (
    product_no SERIAL
);

3、生成列

生成的列是一个特殊的列，它总是从其他列计算而来。
生成列有两种:存储列和虚拟列。
存储生成列在写入(插入或更新)时计算，并且像普通列一样占用存储空间。
虚拟生成列不占用存储空间并且在读取时进行计算。
PostgreSQL目前只实现了存储生成列。

建立一个生成列，在 CREATE TABLE中使用 GENERATED ALWAYS AS 子句, 例如:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

必须指定关键字 STORED 以选择存储类型的生成列。

生成列不能被直接写入. 在INSERT 或 UPDATE 命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。

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

它并没有依附在一个特定的列，而是作为一个独立的项出现在逗号分隔的列列表中。列定义和这种约束定义可以以混合的顺序出现在列表中。
前两个约束称为列约束，而第三个约束为表约束，因为它独立于任何一个列定义。

4.2、非空约束

一个非空约束仅仅指定一个列中不会有空值。

语法例子：

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

NOT NULL约束有一个相反的情况：NULL约束。这并不意味着该列必须为空，进而肯定是无用的。
相反，它仅仅选择了列可能为空的默认行为。
PostgreSQL中加入它是为了和某些其他数据库系统兼容。
例如，初始时我们可以：

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

然后可以在需要的地方插入NOT关键词。

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

4.4、主键

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。

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。
一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主键。

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

因为如果缺少列的列表，则被引用表的主键将被用作被引用列。

一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

当然，被约束列的数量和类型应该匹配被引用列的数量和类型。

限制删除或者级联删除是两种最常见的选项。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 &&)
);

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

http://www.postgres.cn/docs/13/sql-createtable.html#SQL-CREATETABLE-EXCLUDE