Question

第 5 章 数据定义（DDL）

目录

5.1. 模式

5.2. Types

5.2.1. Enumerated Types

5.2.2. uuid

5.3. 表

5.3.1. 默认时间与时区

5.3.2. 汉字做字段名

5.4. 序列

5.4.1. 等差列 “1，2，3，4，5，6，7，8，9…”

5.4.2. 等差列 “1，3，5，7，9…”

5.4.3. 等差列 “2，4，6，8，10…”

5.4.4. n1+n2

5.5. 约束

5.5.1. 检查约束

5.5.2. 非空约束

5.5.3. 唯一约束

5.6. 主键/外键

5.6.1. 主键

5.6.2. 外键约束

5.6.3. 案例

5.1. 模式

3.10       模式

一些用户为了使某些模块的表看起来清晰，一般他们采用“模块名_表名”：
Auth_user
Auth_group
Bbs_topic
Bbs_message

PostgreSQL不必这样命名，可以使用Schema（模式）如：
Auth.user
Auth.group
Bbs.topic
Bbs.message

3.10.1 创建模式

CREATE SCHEMA your_schema;
例：
CREATE SCHEMA btob;
CREATE SCHEMA auction;
3.10.2 删除模式

DROP SCHEMA your_schema;
删除模式，并且同时删除模式下的（表，视图，触发器，过程……）
DROP SCHEMA your_schema CASCADE;
例：
DROP SCHEMA btob CASCADE;
DROP SCHEMA btob CASCADE;
3.10.3 模式搜索路径

查看当前模式SHOW search_path ;
netkiller=> SHOW search_path ;
 search_path
--------------
 $user,public
(1 row)
netkiller=> \dt
            List of relations
 Schema |    Name     | Type  |   Owner
--------+-------------+-------+-----------
 public | company     | table | netkiller
 public | group       | table | netkiller
 public | groupmember | table | netkiller
 public | guestbook   | table | netkiller
 public | prodorder   | table | netkiller
 public | role        | table | netkiller
 public | rolemember  | table | netkiller
 public | system_log  | table | netkiller
 public | templates   | table | netkiller
 public | trust       | table | netkiller
 public | user        | table | netkiller
 public | user_log    | table | netkiller
 public | userinfo    | table | netkiller
(13 rows)
如果不设置模式搜索路径，“\dt”只显示public模式下的表。
设置模式SET search_path TO public,btob,auction;
netkiller=> SET search_path TO public,btob,auction;
SET
netkiller=> \dt
              List of relations
 Schema  |     Name      | Type  |   Owner
---------+---------------+-------+-----------
 auction | messages      | table | netkiller
 auction | product       | table | netkiller
 auction | product_order | table | netkiller
 btob    | directory     | table | netkiller
 btob    | trade         | table | netkiller
 btob    | trade_message | table | netkiller
 public  | company       | table | netkiller
 public  | group         | table | netkiller
 public  | groupmember   | table | netkiller
 public  | guestbook     | table | netkiller
 public  | prodorder     | table | netkiller
 public  | role          | table | netkiller
 public  | rolemember    | table | netkiller
 public  | system_log    | table | netkiller
 public  | templates     | table | netkiller
 public  | trust         | table | netkiller
 public  | user          | table | netkiller
 public  | user_log      | table | netkiller
 public  | userinfo      | table | netkiller
(19 rows)

netkiller=>

-- ======================================================================
-- 'btob.directory'
-- ======================================================================
Drop table btob.directory CASCADE;

Create table btob.directory
(
    "id" Serial NOT NULL,
    "root_id" Integer NOT NULL Default 0,
    "name"    Varchar(20)NOT NULL ,
    "status"   boolean Default 'true',
    "created" Timestamp Default current_timestamp,
    "modified" Timestamp Default current_timestamp,
    UNIQUE (id,root_id,name),
    PRIMARY KEY ("id")
--    FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
);
INSERT INTO btob.directory (id,root_id,name) VALUES (0,0,'/');
Alter table btob.directory add  FOREIGN KEY (root_id) REFERENCES btob.directory (id) ON DELETE CASCADE;
Create index "directory_index" on btob.directory using btree ("id","root_id","name");