postgreSQL |表、约束、索引

2019独角兽企业重金招聘Python工程师标准>>>

参看链接

PG创建表

零、表

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

1. 创建表

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

1.1 创建带有缺省值的表

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

3.3唯一性约束

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

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

    CREATE TABLE tb_products (
        id integer,
        name text,
        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,
        name text,
        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,
        b integer,
        c 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,
        b integer,
        c integer,
        --该外键的字段数量和被引用表中主键的数量必须保持一致。
        FOREIGN KEY (b, c) REFERENCES example (b, c)
    );

3.4.3级联删除

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

    CREATE TABLE tb_products (
        pid integer PRIMARY KEY,
        name text,
        price numeric
    );
    
    CREATE TABLE tb_orders (
        order_id integer PRIMARY KEY,
        shipping_address text
    );
    
    CREATE TABLE tb_order_items (
        pid integer 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;

对于显示命名的约束，可以根据其名称直接删除，对于隐式自动命名的约束，可以通过psql的\d tablename来获取该约束的名字。和删除字段一样，如果你想删除有着被依赖关系地约束，你需要用CASCADE。一个例子是某个外键约束依赖被引用字段上的唯一约束或者主键约束。如：
    MyTest=# \d products
         Table "public.products"
       Column     |  Type   | Modifiers
     ------------+---------+-----------
     product_no | integer |
     name          | text    |
     price           | numeric |
     Check constraints:
        "positive_price" CHECK (price > 0::numeric)

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;

一、索引

转载于:https://my.oschina.net/u/2529084/blog/888743