Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎专栏 | Search | Email

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