零、表

PG提供：普通表、临时表、无日志表、继承表、基于复合类型表、外部表

1. 创建表

  CREATE TABLE tb_products (
    id integer,name text,price numeric
  );

1.1创建带有缺省值的表

  CREATE TABLE tb_products (
    id integer,price numeric DEFAULT 9.99 --DEFAULT关键字，9.99是字段price的默认值。
  );
 
  CREATE TABLE tb_products (
    id SERIAL,--SERIAL自增字段,autonumber
    name text,price numeric DEFAULT 9.99
  );

2. 删除表

  DROP TABLE tb_products;

3. 约束

约束类型：外键约束、唯一性约束、检查约束、排他性约束

3.1检查约束

字段里的数值必须满足一个布尔表达式。此外，也可以声明表级别的检查约束。

CREATE TABLE tb_products (
    id integer,--price字段的值必须大于0，否则插入或修改该字段值，将引发错误。
    --匿名约束，Postgresql将会根据当前的表名、字段名和约束类型，
    --为该约束自动命名，如：products_price_check。
    price numeric CHECK (price > 0)
  );

  CREATE TABLE tb_products (
    id integer,--显式约束，名为positive_price。
        --好处:维护该约束时，可根据该名进行直接操作。
    price numeric CONSTRAINT positive_price CHECK (price > 0)
  );

3.2非空约束

字段不能插入、更新为空值

CREATE TABLE tb_products (
    id integer NOT NULL,name text NOT NULL,price numeric
  );

字段中存在多个约束，定义时不用考虑声明顺序。

  CREATE TABLE tb_products (
    idinteger NOT NULL,price numeric NOT NULL CHECK (price > 0)
  );

3.3唯一性约束

字段不能插入、更新为当前表中的已有值

CREATE TABLE tb_products (
    id integer UNIQUE,price numeric
  );

  CREATE TABLE tb_products (
    id integer,price numeric,UNIQUE (product_no)
  );

3.3.1多字段定义联合唯一性

  CREATE TABLE example (
    a integer,b integer,c integer,UNIQUE (a,c)
  );

3.3.2为唯一性约束命名

  CREATE TABLE tb_products (
    id integer CONSTRAINT must_be_different UNIQUE,price numeric
  );

在插入数据时，空值(NULL)之间被视为不相等的数据，因此对于某一唯一性字段，可以多次插入空值。然而需要注意的是，这一规则并不是被所有数据库都遵守，因此在进行数据库移植时可能会造成一定的麻烦。

3.4主键和外键

主键约束唯一约束和非空约束的组合

  CREATE TABLE tb_products (
    id integer PRIMARY KEY,--字段product_no被定义为该表的唯一主键。
    name text,price numeric
  );

3.4.1联合主键：

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

3.4.2外键（参考完整性）

  CREATE TABLE orders (
    order_id integer PRIMARY KEY,--主键。
    --该表的id字段是外键，为products表主键(id)。
    id integer FOREIGN KEY REFERENCES products(product_no),quantity integer
  ); 
  CREATE TABLE t1 (
    a integer PRIMARY KEY,--该外键的字段数量和被引用表中主键的数量必须保持一致。
    FOREIGN KEY (b,c) REFERENCES example (b,c)
  );

3.4.3级联删除

当多个表存在主外键的参考性约束关系时。如果想删除被参照表某行记录，由于该行记录的主键字段值可能在参照表中某条记录所关联，所以删除操作将会失败。

  CREATE TABLE tb_products (
    pid integer PRIMARY KEY,price numeric
  );
 
  CREATE TABLE tb_orders (
    order_id integer PRIMARY KEY,shipping_address text
  );
 
  CREATE TABLE tb_order_items (
    pidinteger REFERENCES tb_products ON DELETE RESTRICT,--限制选项
    order_id integer REFERENCES tb_orders ON DELETE CASCADE,--级联删除选项
    quantity integer,PRIMARY KEY (pid,order_id)
  );

RESTRICT ：禁止删除被引用的行。

NO ACTION ：的意思是如果在检查约束的时候，如果还存在任何引用行，则抛出错误；如果你不声明任何东西，那么它就是缺省的行为。(这两个选择的实际区别是，NO ACTION 允许约束检查推迟到事务的晚些时候，而 RESTRICT 不行。)

CASCADE ：声明在删除一个被引用的行的时候，引用它的行也会被自动删除掉。在外键字段上的动作还有两个选项： SET NULL 和 SET DEFAULT。这样会导致在被引用行删除的时候，引用它们的字段分别设置为空或者缺省值。请注意这些选项并不能让你逃脱被观察和约束的境地。比如，如果一个动作声明 SET DEFAULT，但是缺省值并不能满足外键，那么动作就会失败。

类似ON DELETE，还有ON UPDATE 选项，它是在被引用字段修改(更新)的时候调用的。可用的动作是一样的。

4表的修改

4.1增加字段

ALTER TABLE tb_products 
ADD COLUMN description text;

###在新增字段时，可以同时给该字段指定约束。

ALTER TABLE tb_products 
ADD COLUMN description text CHECK(description <> '');

4.2删除字段

ALTER TABLE tb_products 
DROP COLUMN description;

如果该表为被参照表，该字段为被引用字段，那么上面的删除操作将会失败。如果要想在删除被引用字段的同时级联的删除其所有引用字段，可以采用下面的语法形式。

ALTER TABLE tb_products 
DROP COLUMN description 
CASCADE;

4.3增加约束

ALTER TABLE tb_products 
ADD CHECK(name <> ''); --增加一个表级约束

ALTER TABLE tb_products 
ADD CONSTRAINT some_name UNIQUE(pid);--增加命名的唯一性约束。

ALTER TABLE tb_products 
ADD FOREIGN KEY(pdt_grp_id) 
       REFERENCES pdt_grps; --增加外键约束。

ALTER TABLE tb_products 
ALTER COLUMN pid SET NOT NULL; --增加一个非空约束。

4. 4删除约束

ALTER TABLE tb_products 
DROP CONSTRAINT some_name;

4.4.1删除非空约束

ALTER TABLE tb_products 
ALTER COLUMN product_no DROP NOT NULL;

4.5改变字段的缺省值

4.5.1为已有字段添加缺省值

ALTER TABLE tb_products ALTER COLUMN price SET DEFAULT 7.77;

4.5.2删除缺省值

ALTER TABLE tb_products ALTER COLUMN price DROP DEFAULT;

4.6修改字段的数据类型

ALTER TABLE tb_products 
ALTER COLUMN price TYPE numeric(10,2);

4.7修改字段名

ALTER TABLE tb_products 
RENAME COLUMN pid TO product_number;

4.8修改表名

ALTER TABLE tb_products RENAME TO items;

一、索引

postgreSQL |表、约束、索引

零、表